--创建测试主表.ID是主键.
CREATETABLEtest_main(
idINT,
valueVARCHAR(10),
PRIMARYKEY(id)
);
--创建测试子表.
CREATETABLEtest_sub(
idINT,
main_idINT,
valueVARCHAR(10),
PRIMARYKEY(id)
);
--插入测试主表数据.
INSERTINTOtest_main(id,value)VALUES(1,'ONE');
INSERTINTOtest_main(id,value)VALUES(2,'TWO');
--插入测试子表数据.
INSERTINTOtest_sub(id,main_id,value)VALUES(1,1,'ONEONE');
INSERTINTOtest_sub(id,main_id,value)VALUES(2,2,'TWOTWO');
SQL>--创建外键(默认选项)
SQL>ALTERTABLEtest_subADDCONSTRAINTmain_id_consFOREIGNKEY(main_id)REFERENCEStest_main;
Tablealtered.
SQL>
SQL>--测试删除主表数据.将出错ORA-02292:违反完整约束条件
SQL>DELETE
2test_main
3WHERE
4ID=1;
DELETE
*
ERRORatline1:
ORA-02292:integrityconstraint(HR.MAIN_ID_CONS)violated-childrecordfound
测试完毕后，删除外键约束
ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;
SQL>--创建外键(使用ONDELETECASCADE选项，删除主表的时候，同时删除子表)
SQL>ALTERTABLEtest_sub
2ADDCONSTRAINTmain_id_cons
3FOREIGNKEY(main_id)REFERENCEStest_mainONDELETECASCADE;
Tablealtered.
SQL>
SQL>--测试删除主表数据.将成功地执行.
SQL>DELETE
2TEST_MAIN
3WHERE
4ID=1;
1rowdeleted.
SQL>
SQL>--测试检索子表，应该只有一条main_id=2的数据.
SQL>SELECT
2*
3FROM
4test_sub;
IDMAIN_IDVALUE
----------------------------------------
22TWOTWO
测试完毕后，删除外键约束
ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;
SQL>--创建外键(使用ONDELETESETNULL选项，删除主表的时候，同时将子表的main
_id设置为NULL)
SQL>ALTERTABLEtest_sub
2ADDCONSTRAINTmain_id_cons
3FOREIGNKEY(main_id)REFERENCEStest_mainONDELETESETNULL;
Tablealtered.
SQL>
SQL>
SQL>--测试删除主表数据.将成功地执行.
SQL>DELETE
2TEST_MAIN
3WHERE
4ID=2;
1rowdeleted.
SQL>
SQL>--测试检索子表，应该有一条的main_id=null
SQL>SELECT
2*
3FROM
4test_sub;
IDMAIN_IDVALUE
----------------------------------------
2TWOTWO
测试完毕后，删除外键约束
ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;
启用/禁用
当遇到批量数据导入的时候，如果外键处于有效的状态。
那么要求数据导入的程序，必须先导入主表的数据，然后再导入子表的数据。
如果表多，且关系复杂的话，会增加很大的工作量。
可以在数据导入以前，先暂时禁用外键约束。
子表、主表数据都导入完毕后，再启用外键约束。
以下测试环境为初始的创建表、初始数据的情况。
SQL>--创建外键(默认选项)
SQL>ALTERTABLEtest_subADDCONSTRAINTmain_id_consFOREIGNKEY(main_id)REFERENCEStest_main;
表已更改。
SQL>--测试插入子表数据.将出错ORA-02291:违反完整约束条件
SQL>INSERTINTOtest_sub(ID,MAIN_ID,VALUE)VALUES(3,3,'THREETHREE');
INSERTINTOtest_sub(ID,MAIN_ID,VALUE)VALUES(3,3,'THREETHREE')
*
ERROR位于第1行:
ORA-02291:违反完整约束条件(TEST.MAIN_ID_CONS)-未找到父项关键字
--尝试禁用外键约束.
SQL>ALTERTABLEtest_subMODIFYCONSTRAINTmain_id_consDISABLE;
表已更改。
SQL>INSERTINTOtest_sub(ID,MAIN_ID,VALUE)VALUES(3,3,'THREETHREE');
已创建1行。
SQL>INSERTINTOtest_main(id,value)VALUES(3,'THREE');
已创建1行。
SQL>commit;
提交完成。
--恢复启用外键约束.
SQL>ALTERTABLEtest_subMODIFYCONSTRAINTmain_id_consENABLE;
表已更改。
SQL>DELETEtest_mainWHEREID=1;
DELETEtest_mainWHEREID=1
*
ERROR位于第1行:
ORA-02292:违反完整约束条件(TEST.MAIN_ID_CONS)-已找到子记录
测试完毕后，删除外键约束
ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;
延迟约束
在编程开发环境中，可能会遇到，要先插入子表，然后插入主表。
或者先更新主表的主键，然后更新子表的外键的情况。
由于是开发环境下，因此不适合使用先DISABLE再ENABLE的处理机制。
原因是程序中可能会存在并发处理
也就是
用户ADISABLE--更新处理--ENABLE
用户BDISABLE--更新处理--ENABLE
这种情况下，需要启用延迟约束
也就是INSERT/UPDATE语句执行的时候，暂时不检查数据的完整性，等到Commit的时候，统一做检查。
SQL>--创建外键(默认选项)
SQL>ALTERTABLEtest_subADDCONSTRAINTmain_id_cons
2FOREIGNKEY(main_id)REFERENCEStest_main
3DEFERRABLEINITIALLYDEFERRED;
表已更改。
SQL>INSERTINTOtest_sub(ID,MAIN_ID,VALUE)VALUES(4,4,'FOURFOUR');
已创建1行。
SQL>commit;
commit
*
ERROR位于第1行:
ORA-02091:事务处理已回退
ORA-02291:违反完整约束条件(TEST.MAIN_ID_CONS)-未找到父项关键字
SQL>INSERTINTOtest_sub(ID,MAIN_ID,VALUE)VALUES(4,4,'FOURFOUR');
已创建1行。
SQL>INSERTINTOtest_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 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.
