oracle 主外键管理

使用主外键约束使得数据具有完整性。

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.

说白了，就是外键列必须创建索引。