Oracle 外键约束

缺乏、安全感 2021-12-17 12:27 353阅读 0赞

下面的语句创建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 异常表不存在，则此语句将失败。

转载于:https://www.cnblogs.com/zhaochunyi/p/10876038.html