父表修改与外键的关系（主键DML与外键的关系）

Data Integrity学习（二）

 

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

 

DML Statement	Issued Against Parent Table	Issued Against Child Table
INSERT	Always OK if the parent key value is unique	OK only if the foreign key value exists in the parent key or is partially or all null
UPDATE NO ACTION	Allowed if the statement does not leave any rows in the child table without a referenced parent key value	Allowed if the new foreign key value still references a referenced key value
DELETE NO ACTION	Allowed if no rows in the child table reference the parent key value	Always OK
DELETE CASCADE	Always OK	Always OK
DELETE SET NULL	Always OK	Always OK

 

主键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】

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-753900/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-753900/