Oracle主键和外键详解及实用技巧

在 Oracle 数据库中，主键（Primary Key）和外键（Foreign Key）用于维护数据库表之间的数据完整性。

1. 主键（Primary Key）

主键是一列或多列，能够唯一标识表中的每一行。表中只能有一个主键，并且主键列不能为空（即 NOT NULL）。

特性：

• 唯一性：主键中的每一个值都是唯一的，不能重复。
• 非空性：主键列不能包含 NULL 值。
• 索引：Oracle 自动为主键创建唯一索引，提升查询性能。

常用操作：

1. 创建表时指定主键

   CREATE TABLE employees (
       emp_id NUMBER PRIMARY KEY, 
       emp_name VARCHAR2(100), 
       department_id NUMBER
   );
   

   在 emp_id 列上创建了主键。

2. 为现有表添加主键
   如果表已经存在，但还没有主键，可以使用 ALTER TABLE 命令添加主键。

   ALTER TABLE employees ADD CONSTRAINT pk_emp_id PRIMARY KEY (emp_id);
   

3. 删除主键
   如果需要删除主键，可以使用以下语法：

   ALTER TABLE employees DROP PRIMARY KEY;
   

4. 查看主键
   查询某张表的主键可以通过 USER_CONSTRAINTS 视图：

   SELECT constraint_name 
   FROM user_constraints 
   WHERE table_name = 'EMPLOYEES' AND constraint_type = 'P';
   

2. 外键（Foreign Key）

外键用于在两个表之间建立关联，通常是从一个表（子表）的列引用另一个表（父表）的主键。外键用来确保引用完整性，意味着外键列的值必须存在于父表的主键中。

特性：

• 引用完整性：外键保证子表中的某些值在父表中必须存在。
• 级联操作：在父表中删除或更新主键时，外键可以选择级联操作（CASCADE）。

常用操作：

1. 创建表时指定外键
   在创建表时，可以同时指定外键约束：

   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 表的外键。

2. 为现有表添加外键
   可以使用 ALTER TABLE 语句为已经存在的表添加外键：

   ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);
   

3. 删除外键
   如果需要删除外键，可以使用以下语法：

   ALTER TABLE employees DROP CONSTRAINT fk_department;
   

4. 级联删除（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 表中对应的行也会被删除。

5. 查看外键
   查询某张表的外键可以通过 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 中不支持此选项。

示例操作：

1. 带外键和主键的表操作

   -- 创建部门表，包含主键
   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
   );
   

2. 插入和删除数据时的外键行为

   -- 向父表插入数据
   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 数据库可以有效地维护数据的完整性和关联性。