oracle设置表的主键和外键,Oracle 父表修改与外键的关系（主键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

】