Oracle 外键约束

下面的语句创建department_20表，并定义和启用department_id列上的外键，该外键引用departments表的department_id列上的主键：

CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id   CONSTRAINT fk_deptno 
                    REFERENCES departments(department_id) ); 

 

约束fk_deptno确保dept_20表中为员工指定的所有部门都存在于departments表中。但是，员工可以拥有空的部门编号，这意味着他们没有分配给任何部门。为了确保所有员工都被分配到一个部门，除了引用约束之外，您还可以在dept_20表的department_id列上创建一个非空约束。

在定义和启用此约束之前，必须定义并启用departments表的department_id列的主键或唯一约束。

外键约束定义不使用foreign key子句，因为约束是以内联方式定义的。不需要Department_ID列的数据类型，因为Oracle会自动为此列分配引用键的数据类型。

约束定义标识被引用键的父表和列。因为被引用的键是父表的主键，所以被引用的键列名是可选的。

或者，您可以不按行定义此外键约束：

CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id, 
   CONSTRAINT fk_deptno 
      FOREIGN  KEY (department_id) 
      REFERENCES  departments(department_id) ); 

 

此语句两个变体中的外键定义都省略了on delete子句，从而导致Oracle在某个部门中有员工工作时，阻止删除该部门。

ON DELETE 示例

此语句创建dept_20表，定义并启用两个引用完整性约束，并使用on delete子句：

CREATE TABLE dept_20 
   (employee_id     NUMBER(4) PRIMARY KEY, 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4) CONSTRAINT fk_mgr
                    REFERENCES employees ON DELETE SET NULL, 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id   NUMBER(2)   CONSTRAINT fk_deptno 
                    REFERENCES departments(department_id) 
                    ON DELETE CASCADE ); 

 

由于第一个on delete子句，如果从Employees表中删除了经理编号2332，则Oracle将dept_20表中以前拥有经理2332的所有员工的经理ID值设置为空。

由于存在第二个on delete子句，Oracle将departments表中department_id值的任何删除操作级联到department_20表中依赖行的department_id值。例如，如果从Departments表中删除Department 20，则Oracle将从Department_20表中删除Department 20中的所有员工。

复合外键约束示例

以下语句定义并启用dept_20表的employee_id和hire_date列组合的外键：

ALTER TABLE dept_20
   ADD CONSTRAINT fk_empid_hiredate
   FOREIGN KEY (employee_id, hire_date)
   REFERENCES hr.job_history(employee_id, start_date)
   EXCEPTIONS INTO wrong_emp;

约束fk_empid_hiredate确保dept_20表中的所有员工都具有员工表中存在的员工ID和雇用日期组合。在定义和启用此约束之前，必须定义并启用一个约束，该约束将Employees表的Employee_ID和Hire_Date列的组合指定为主键或唯一键。

EXCEPTIONS INTO 子句导致Oracle将有关dept_20表中违反约束的任何行的信息写入 wrong_emp表。如果 wrong_emp 异常表不存在，则此语句将失败。