使用主外键约束使得数据具有完整性。
1、查询表上所有的约束
select * from user_constraints t
where t.table_name='FATHER';
2、查询具有主外键关系的表
select c.owner,c.constraint_name,c.constraint_type,c.table_name,f.owner,f.constraint_name,f.constraint_type,f.table_name
from dba_constraints c, dba_constraints f
where c.r_owner=f.owner
and c.r_constraint_name=f.constraint_name
and c.table_name='CHILD'; --查询子表CHILD对应的所有父表
3、子表中插入的记录必须在父表中存在，否则会报parent key not found
SQL> insert into child values ('datong',1);
insert into child values ('datong',1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_ADDR) violated - parent key not found
4、父表的记录只有在子表中找不到才可以删除，否则会报child record found，除非子表的外键约束on delete=cascade/set null
SQL> delete from father where id=1;
delete from father where id=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_ID) violated - child record found
SQL> delete from father where id=2;
1 row deleted.
SQL> commit;
Commit complete.
5、如何完全删除父表数据，如truncate、drop
SQL> truncate table father;
truncate table father
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
针对上面情况，可以先将father表的所有子表的引用约束disable，使用下面的sql得到禁用子表约束语句：
select 'alter table '||c.owner||'.'||c.table_name||' modify constraint '||c.constraint_name||' disable;' "exec_sql"
from user_constraints c, user_constraints f
where c.r_owner=f.owner
and c.r_constraint_name=f.constraint_name
and f.table_name='FATHER';
exec_sql
-------------------------------------
alter table SCOTT.CHILD modify constraint FK_ID disable;
然后执行上面的查询结果，就可以禁掉所有的子表约束，truncate父表就不会报错了。
SQL> alter table SCOTT.CHILD modify constraint FK_ID disable;
Table altered.
SQL> truncate table father;
Table truncated.
当然，此时子表的引用约束不一定能起来（enable），取决于子表是否有数据。
SQL> alter table SCOTT.CHILD modify constraint FK_ID enable;
alter table SCOTT.CHILD modify constraint FK_ID enable
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_ID) - parent keys not found
将子表数据全部删除，就可以起来（enable）子表的引用约束。
SQL> truncate table child;
Table truncated.
SQL> alter table SCOTT.CHILD modify constraint FK_ID enable;
Table altered.
SQL> drop table father;
drop table father
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
这种情况可以使用cascade constraints子句一同将子表的引用约束删掉。
SQL> drop table father cascade constraints;
Table dropped.
6. 外键列必须创建索引
案例一：外键列无索引，删除主表数据
--主表
CREATE TABLE supplier
( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');
INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');
COMMIT;
--子表
CREATE TABLE product
( product_id number(10) not null,
product_name varchar2(50) not null,
supplier_id number(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
on delete cascade
);
INSERT INTO product VALUES (1, 'Product 1', 1);
INSERT INTO product VALUES (2, 'Product 2', 1);
INSERT INTO product VALUES (3, 'Product 3', 2);
COMMIT;
--在执行下面的操作之前，先查看数据库锁的情况
SQL> select * from v$lock t
2 where t.TYPE in ('TX','TM')
3 order by t.SID
4 /
no rows selected
SESSION1(SID=269):
SQL> delete from supplier where supplier_id=1;
1 row deleted.
SESSION2(SID=270):
SQL> delete from supplier where supplier_id=2;
(现象hang住了)
SESSION3(SID=271):
SQL> insert into supplier values (3,'supplier 3','Contact 3');
(现象hang住了)
--此时再查看锁的情况
SQL> select * from v$lock t
2 where t.TYPE in ('TX','TM')
3 order by t.SID
4 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
34FC7378 34FC7494 269 TX 262156 112 6 0 225 0
34F47858 34F47870 269 TM 9941 0 3 0 225 1
34F477AC 34F477C4 269 TM 9939 0 3 0 225 0
34F47904 34F4791C 270 TM 9939 0 3 0 174 0
34F479B0 34F479C8 270 TM 9941 0 0 5 174 0
34F47B08 34F47B20 271 TM 9941 0 0 2 123 0
34F47A5C 34F47A74 271 TM 9939 0 3 0 123 0
7 rows selected.
可以看到SESSION1(SID=269)获得了一个行锁（type=TX lmode=6)，同时两个表锁（type=TM lmode=3）;
SESSION2(SID=270)获得了一个表锁（type=TM lmode=3），同时请求一个表锁（type=TM request=5 id1=9941）
SESSION3(SID=271)同样获得了一个表锁（type=TM lmode=3），同时请求一个表锁（type=TM request=2 id1=9941）。
--将外键列创建索引，进行上面同样的操作，查看是否有锁等待。
create index ind_product on product (supplier_id) online nologging;
SESSION1(SID=269):
SQL> delete from supplier where supplier_id=1;
1 row deleted.
SESSION2(SID=270):
SQL> delete from supplier where supplier_id=2;
1 row deleted.
SESSION3(SID=271):
SQL> insert into supplier values (3,'supplier 3','Contact 3');
1 row created.
发现此时就没有了锁表的情况，查看数据库锁的情况：
SQL> select * from v$lock t
2 where t.TYPE in ('TX','TM')
3 order by t.SID
4 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
34FC7378 34FC7494 269 TX 458771 120 6 0 204 0
34F477AC 34F477C4 269 TM 9939 0 3 0 204 0
34F47858 34F47870 269 TM 9941 0 3 0 204 0
34FC78A4 34FC79C0 270 TX 131076 112 6 0 162 0
34F47904 34F4791C 270 TM 9939 0 3 0 162 0
34F479B0 34F479C8 270 TM 9941 0 3 0 162 0
34F47B08 34F47B20 271 TM 9941 0 2 0 81 0
34FC7DC0 34FC7EDC 271 TX 655361 113 6 0 81 0
34F47A5C 34F47A74 271 TM 9939 0 3 0 81 0
9 rows selected.
说白了，就是外键列必须创建索引。