ORACLE外键约束

-- 创建测试主表. ID 是主键.

CREATE TABLE test_main (

 id      INT,

 value   VARCHAR(10),

 PRIMARY KEY(id)  

);

 

 

-- 创建测试子表. 

CREATE TABLE test_sub (

  id      INT,

 main_id INT,

 value   VARCHAR(10),

 PRIMARY KEY(id)  

);

 

 

-- 插入测试主表数据.

INSERT INTO test_main(id, value) VALUES (1,'ONE');

INSERT INTO test_main(id, value) VALUES (2,'TWO');

 

-- 插入测试子表数据.

INSERT INTO test_sub(id, main_id, value)VALUES (1, 1, 'ONEONE');

INSERT INTO test_sub(id, main_id, value)VALUES (2, 2, 'TWOTWO');

 

 

 

默认外键约束方式

 

SQL> -- 创建外键(默认选项)

SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons  FOREIGN KEY (main_id)  REFERENCES test_main;

 

Table altered.

 

SQL>

SQL> -- 测试删除主表数据. 将出错 ORA-02292:违反完整约束条件

SQL> DELETE

 2    test_main

 3  WHERE

 4    ID = 1;

DELETE

*

ERROR at line 1:

ORA-02292: integrity constraint(HR.MAIN_ID_CONS) violated - child record found

 

 

测试完毕后，删除外键约束

ALTER TABLE test_subDROP CONSTRAINT main_id_cons;

 

 

 

 

 

DELETE CASCADE 方式

 

SQL> -- 创建外键(使用 ON DELETECASCADE 选项，删除主表的时候，同时删除子表)

SQL> ALTER TABLE test_sub

  2    ADDCONSTRAINT main_id_cons

  3     FOREIGN KEY (main_id) REFERENCES  test_main  ON DELETE  CASCADE;

 

Table altered.

 

SQL>

SQL> -- 测试删除主表数据. 将成功地执行.

SQL> DELETE

 2    TEST_MAIN

 3  WHERE

 4    ID = 1;

 

1 row deleted.

 

SQL>

SQL> -- 测试检索子表，应该只有一条 main_id = 2 的数据.

SQL> SELECT

 2    *

 3  FROM

 4    test_sub;

 

       ID    MAIN_ID VALUE

---------- ---------- --------------------

         2          2 TWOTWO

 

 

测试完毕后，删除外键约束 

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

 

 

 

SET NULL方式

 

SQL> -- 创建外键(使用 ON DELETESET NULL 选项，删除主表的时候，同时将子表的 main

_id 设置为 NULL)

SQL> ALTER TABLE test_sub

  2   ADD CONSTRAINT main_id_cons

  3     FOREIGN KEY (main_id) REFERENCES  test_main  ON DELETE SET NULL;

 

Table altered.

 

SQL>

SQL>

SQL> -- 测试删除主表数据. 将成功地执行.

SQL> DELETE

 2    TEST_MAIN

 3  WHERE

 4    ID = 2;

 

1 row deleted.

 

SQL>

SQL> -- 测试检索子表，应该有一条的 main_id = null

SQL> SELECT

 2    *

 3  FROM

 4    test_sub;

 

       ID    MAIN_ID VALUE

---------- ---------- --------------------

         2            TWOTWO

 

测试完毕后，删除外键约束 

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

 

 

 

 

 

 

启用/禁用

 

当遇到批量数据导入的时候，如果外键处于有效的状态。
那么要求 数据导入的程序， 必须先导入主表的数据，然后再导入子表的数据。
如果表多，且关系复杂的话，会增加很大的工作量。

可以在数据导入以前， 先暂时禁用外键约束。
子表、主表数据都导入完毕后， 再启用外键约束。

 

以下测试环境为 初始的创建表、初始数据的情况。

 

SQL> -- 创建外键(默认选项)
SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons  FOREIGN KEY (main_id)  REFERENCES  test_main;

表已更改。

SQL> -- 测试插入子表数据. 将出错 ORA-02291: 违反完整约束条件
SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE')
*
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 未找到父项关键字

 

 

-- 尝试禁用外键约束.
SQL> ALTER TABLE test_sub  MODIFY   CONSTRAINT main_id_cons DISABLE;
表已更改。

SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
已创建 1 行。

SQL> INSERT INTO test_main(id, value) VALUES (3, 'THREE');
已创建 1 行。

SQL> commit;
提交完成。

 

 

-- 恢复启用外键约束.
SQL> ALTER TABLE test_sub  MODIFY   CONSTRAINT main_id_cons  ENABLE;
表已更改。

SQL> DELETE test_main  WHERE ID = 1;
DELETE test_main  WHERE ID = 1
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 已找到子记录

 

 

测试完毕后，删除外键约束 

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

 

 

 

 

延迟约束

 

在编程开发环境中，可能会遇到，要先插入子表， 然后插入主表。
或者先更新主表的 主键， 然后更新子表的外键的情况。

由于是开发环境下，因此不适合使用 先 DISABLE 再 ENABLE 的处理机制。
原因是程序中可能会存在并发处理

也就是

用户A   DISABLE -- 更新处理  -- ENABLE
用户B              DISABLE -- 更新处理  -- ENABLE

这种情况下，需要启用 延迟约束
也就是 INSERT / UPDATE 语句执行的时候， 暂时不检查数据的完整性，等到 Commit 的时候，统一做检查。

 

 

 

SQL> -- 创建外键(默认选项)
SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons 
  2    FOREIGN KEY (main_id)  REFERENCES  test_main 
  3    DEFERRABLE   INITIALLY DEFERRED;
表已更改。

SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (4, 4, 'FOURFOUR');

已创建 1 行。

SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已回退
ORA-02291: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 未找到父项关键字

 

SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (4, 4, 'FOURFOUR');
已创建 1 行。

SQL> INSERT INTO test_main(id, value) VALUES (4, 'FOUR');
已创建 1 行。

SQL> commit;
提交完成。

 

外键索引

啥时不需要外键索引？
Expert one-on-one Oracle中提到：

• You do not delete from the parent table.

• You do not update the parent tables unique/primary key value, either purposely or by accident (via a tool).

这两种情况下，没有外键索引，Oracle会锁子表，当使用ON DELETE CASCADE时，会进行全表扫描。

• You do not join from the parent table to the child table, of more generally ‐ the foreign key columns do not support an important access path to the child table.

利用join查询时，没有外键索引，Oracle就会进行全表扫描。

当确认这些情况不会出现时，便可以不添加外键索引，但是，如何监测子表是否被锁呢？
Oracle有一个机制：
ALTER TABLE <child table name> DISABLE TABLE LOCK;
这时，UPDATE和DELETE获取锁是都会失败。

 

不从父表删除记录很难避免，因此，如果没有外键索引，将很容易导致死锁，缺外键索引是死锁的主要原因，另外，位图索引的并发更新也是可能导致死锁。

 

以下是ORACLE Database Concept上的描述，记录一下：

Indexes and Foreign Keys

As a rule, foreign keys should be indexed. The only exception is when the matching unique or primary key is never updated or deleted. Indexing the foreign keys in child

tables provides the following benefits:

■ Prevents a full table lock on the child table. Instead, the database acquires a row

lock on the index.

■ Removes the need for a full table scan of the child table. As an illustration,

assume that a user removes the record for department 10 from the departments

table. If employees.department_id is not indexed, then the database must scan

employees to see if any employees exist in department 10.