父表修改與外來鍵的關係（主鍵DML與外來鍵的關係）

lovehewenyu發表於2013-02-04

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章