Data Integrity 学习（二）

父表修改与外键的关系（主键 DML 与外键的关系） 【这张表就是本文重点】

主键 DML 与外键的关系 ：

1 、 INSERT: 主键可以匹配数值，允许外键列插入

2 、 UPDATE NO ACTION ：主键更新没有匹配的数据是允许的，更新被匹配的数据不允许

3 、 DELETE NO ACTION ：主键删除没有被匹配的数据时允许的，删除被匹配的数据不允许

4 、 DELETE CASCADE ：使用了 DELETE CASCADE ，删除主键列数据，会级联删除外键列相应的数据

5 、 DELETE SET NULL ：使用了 DELETE SET NULL ，删除主键列数据，会把外键列相应的数据 DELETE SET NULL

实验：

Parent table : t3    primary key : t3.id

Child table: t3_fk   foreign key : t3_fk.id

1 、 DML Statement INSERT

doudou@TEST> insert into t3 values (2,'3');

1 row created.

doudou@TEST> commit;

Commit complete

doudou@TEST> insert into t3_fk values (2,'fk_2');

1 row created.

doudou@TEST> commit;

Commit complete

【外键列插入的数据在父表主键列能相应的匹配到，允许外键列数据插入】

2 、 DML Statement UPDATE NO ACTION

doudou@TEST> select * from t3;

        ID NAME

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

         1 1

         2 3

         3 pk_3

doudou@TEST> select * from t3_fk;

        ID NAME

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

         1 pk_1

           pk_2

         2 fk_2

doudou@TEST> update t3 set id=4 where id=3;

1 row updated.

doudou@TEST> update t3 set id=5 where id=2;

update t3 set id=5 where id=2

*

ERROR at line 1:

ORA-02292: integrity constraint (DOUDOU.SYS_C007471) violated - child record found

doudou@TEST> select * from t3;

        ID NAME

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

         1 1

         2 3

         4 pk_3

doudou@TEST> update   t3_fk set id=3 where id=2;

update   t3_fk set id=3 where id=2

*

ERROR at line 1:

ORA-02291: integrity constraint (DOUDOU.SYS_C007471) violated - parent key not found

doudou@TEST> update t3_fk set id=4 where id=2;

1 row updated.

【父表 update ： update 主键列没有被外键相关联的数据，是可以更新的。

子表 update ： update 外键列的数据，更改后的数据在主键列可以找到相应的匹配是允许修改的。找不到不允许修改】

3 、 DML Statement DELETE NO ACTION

doudou@TEST> select * from t3;

        ID NAME

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

         1 1

         2 3

         4 pk_3

         3 pk_3

doudou@TEST> select   * from t3_fk;

        ID NAME

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

         1 pk_1

           pk_2

         4 fk_2

doudou@TEST> delete t3 where id=3;

1 row deleted.

doudou@TEST> delete t3 where id=4;

delete t3 where id=4

*

ERROR at line 1:

ORA-02292: integrity constraint (DOUDOU.SYS_C007471) violated - child record found

doudou@TEST> delete t3_fk where id=4;

1 row deleted.

【父表 delete ： delete 主键列没有被外键列相匹配的数据，是允许的。已经被匹配的不允许删除，可以先删除子表匹配的数据，然后删除主键列被匹配的数据。

子表 delete ： delete 外键列的数据是被允许的。】

4 、 DML Statement DELETE CASCADE

doudou@TEST> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name ='T3_FK';

CONSTRAINT_NAME                 CO TABLE_NAME

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

SYS_C007471                     R   T3_FK

doudou@TEST> alter table t3_fk drop constraints SYS_C007471;

Table altered.

doudou@TEST> alter table t3_fk add constraints t3_fk_id foreign key (id) references t3 (id) on delete cascade; --delete cascade 语法

Table altered.

doudou@TEST> select * from t3;

        ID NAME

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

         1 1

         2 3

         4 pk_3

doudou@TEST> select * from t3_fk;

        ID NAME

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

         1 pk_1

           pk_2

doudou@TEST> delete t3 where id=1;

1 row deleted.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t3;

        ID NAME

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

         2 3

         4 pk_3

doudou@TEST> select * from t3_fk;

        ID NAME

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

           pk_2

doudou@TEST> delete t3_fk;

1 row deleted.

【外键约束使用 delete cascade ：删除主键列的数据会级联把外键相关联的数据 delete 】

5 、 DML Statement DELETE SET NULL

doudou@TEST>   select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name ='T3_FK';

CONSTRAINT_NAME                 CO TABLE_NAME

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

T3_FK_ID                        R   T3_FK

doudou@TEST> alter   table t3_fk drop constraints t3_fk_id;

Table altered.

doudou@TEST> alter table t3_fk add constraint t3_fk_id foreign key (id) references t3 (id) on delete set null; --delete set null 语法

Table altered.

doudou@TEST> select * from t3;

        ID NAME

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

         2 3

         4 pk_3

doudou@TEST> select * from t3_fk;

        ID NAME

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

         2 fk_2

doudou@TEST> delete t3 where id=2;

1 row deleted.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t3;

        ID NAME

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

         4 pk_3

doudou@TEST> select * from t3_fk;

        ID NAME

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

           fk_2

【外键约束使用 delete set null : 删除主键的数据会使外键列相应的数据 delete set null 】