在 Oracle 数据库中,主键(Primary Key)和外键(Foreign Key)用于维护数据库表之间的数据完整性。
1. 主键(Primary Key)
主键是一列或多列,能够唯一标识表中的每一行。表中只能有一个主键,并且主键列不能为空(即 NOT NULL)。
特性:
- 唯一性:主键中的每一个值都是唯一的,不能重复。
- 非空性:主键列不能包含 NULL 值。
- 索引:Oracle 自动为主键创建唯一索引,提升查询性能。
常用操作:
-
创建表时指定主键
CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), department_id NUMBER );
在
emp_id
列上创建了主键。 -
为现有表添加主键
如果表已经存在,但还没有主键,可以使用ALTER TABLE
命令添加主键。ALTER TABLE employees ADD CONSTRAINT pk_emp_id PRIMARY KEY (emp_id);
-
删除主键
如果需要删除主键,可以使用以下语法:ALTER TABLE employees DROP PRIMARY KEY;
-
查看主键
查询某张表的主键可以通过USER_CONSTRAINTS
视图:SELECT constraint_name FROM user_constraints WHERE table_name = 'EMPLOYEES' AND constraint_type = 'P';
2. 外键(Foreign Key)
外键用于在两个表之间建立关联,通常是从一个表(子表)的列引用另一个表(父表)的主键。外键用来确保引用完整性,意味着外键列的值必须存在于父表的主键中。
特性:
- 引用完整性:外键保证子表中的某些值在父表中必须存在。
- 级联操作:在父表中删除或更新主键时,外键可以选择级联操作(CASCADE)。
常用操作:
-
创建表时指定外键
在创建表时,可以同时指定外键约束:CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) ); CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), department_id NUMBER, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) );
这里
employees
表中的department_id
列是departments
表的外键。 -
为现有表添加外键
可以使用ALTER TABLE
语句为已经存在的表添加外键:ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);
-
删除外键
如果需要删除外键,可以使用以下语法:ALTER TABLE employees DROP CONSTRAINT fk_department;
-
级联删除(ON DELETE CASCADE)
当删除父表中的记录时,可以设置外键的行为,例如级联删除子表中的相关记录:ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE;
当父表
departments
中的department_id
被删除时,employees
表中对应的行也会被删除。 -
查看外键
查询某张表的外键可以通过USER_CONSTRAINTS
视图:SELECT constraint_name FROM user_constraints WHERE table_name = 'EMPLOYEES' AND constraint_type = 'R';
3. 外键的约束选项
- ON DELETE CASCADE:当父表中的主键记录被删除时,子表中相关的记录也自动删除。
- ON DELETE SET NULL:当父表中的主键记录被删除时,子表中相关的外键列设置为 NULL。
- ON UPDATE CASCADE:在 Oracle 中不支持此选项。
示例操作:
-
带外键和主键的表操作
-- 创建部门表,包含主键 CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) ); -- 创建员工表,包含外键引用 CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), department_id NUMBER, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE );
-
插入和删除数据时的外键行为
-- 向父表插入数据 INSERT INTO departments (department_id, department_name) VALUES (1, 'HR'); INSERT INTO departments (department_id, department_name) VALUES (2, 'IT'); -- 向子表插入数据 INSERT INTO employees (emp_id, emp_name, department_id) VALUES (101, 'Alice', 1); INSERT INTO employees (emp_id, emp_name, department_id) VALUES (102, 'Bob', 2); -- 删除父表数据时,子表中的相关记录也会被删除 DELETE FROM departments WHERE department_id = 1;
通过使用主键和外键,Oracle 数据库可以有效地维护数据的完整性和关联性。