Oracle 创建外键可选用参数说明

（一）、概叙

oracle从9i开始，在主表操作更新删除时，对子表参照数据处理方式说明：

1：使用默认的创建方式，在删除被参照的数据时，将无法被删除；

2：on delete cascade关键字来决定在删除被参照数据时是否要将参照这个数据的那些数据一并删除，即所谓的级联删除；

3：on delete set null关键字来决定是将那些参照这条数据的数据的对应值赋空。

4：deferrable initially deferred 关键字主要作用是在使用更新操作时，延期外键检测；以此来保证更新操作成功；如果没有此关键字，则需在主表内添加触发器来完成更新操作。

(二)、实例表创建

2.1：创建父表TEST

SQL> create table TEST(

2 A NUMBER not null,

3 B VARCHAR2(10) not null,

4 C VARCHAR2(10),

5 D VARCHAR2(10),

6 E VARCHAR2(10)

7 );

表已创建。

SQL> alter table TEST add constraint TEST_PRI primary key (A, B);

表已修改。

2.2：创建TEST对应子表AAA

SQL> create table AAA(

2 A NUMBER,

3 B VARCHAR2(10),

4 C VARCHAR2(10),

5 D VARCHAR2(10),

6 E VARCHAR2(10)

7 );

表已创建。

（三）、分类讨论

3．1默认情况下，语句如下外键

SQL> alter table AAA add constraint AAA_PRI foreign key (A, B)

2 references TEST (A, B);

表已更改。

 

SQL> insert into test values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> insert into aaa values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> delete from test;

delete from test

*

第 1 行出现错误:

ORA-02292: 违反完整约束条件 (LIC.AAA_PRI) - 已找到子记录

 

 

SQL> delete from aaa;

 

已删除 1 行。

 

SQL> select * from aaa;

 

未选定行

 

SQL> delete from test;

 

已删除 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

未选定行

3．2使用on delete cascade 关键字创建外键

SQL> ALTER TABLE AAA DROP CONSTRAINT AAA_PRI;

 

表已更改。

 

SQL> alter table AAA

2 add constraint AAA_PRI foreign key (A, B)

3 references TEST (A, B) on delete cascade;

 

表已更改。

 

SQL> commit;

 

提交完成。

 

SQL> insert into test values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> insert into aaa values(1,'b,'c','d','e');

ERROR:

ORA-01756: 引号内的字符串没有正确结束

 

SQL> insert into aaa values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> delete from test;

 

已删除 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

未选定行

 

SQL> select * from aaa;

 

未选定行

3．3使用on delete cascade 关键字创建外键

SQL> alter table aaa drop constraint AAA_PRI;

 

表已更改。

 

SQL> COMMIT;

 

提交完成。

 

SQL> alter table AAA

2 add constraint AAA_PRI foreign key (A, B)

3 references TEST (A, B) on delete set null;

 

表已更改。

 

SQL> commit;

 

提交完成。

 

SQL> insert into test values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> insert into aaa values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from aaa;

 

A B C D E

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

c d e

1 b c d e

 

SQL> delete from test;

 

已删除 1 行。

 

SQL> select * from aaa;

 

A B C D E

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

c d e

c d e

SQL> select * from test;

 

未选定行

3．4使用deferrable initially deferred 关键字创建外键

3.4.1 触发器实现

SQL> alter table AAA

2 add constraint AAA_PRI foreign key (A, B)

3 references TEST (A, B);

 

表已更改。

 

SQL> commit

2 ;

 

提交完成。

 

SQL> insert into test values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> insert into aaa values(1,'b','c','d','e');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

A B C D E

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

1 b c d e

 

SQL> select * from aaa;

 

A B C D E

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

1 b c d e

 

SQL> update test set a-10;

update test set a-10

*

第 1 行出现错误:

ORA-00927: 缺失等号

 

 

SQL> update test set a=10;

update test set a=10

*

第 1 行出现错误:

ORA-02292: 违反完整约束条件 (LIC.AAA_PRI) - 已找到子记录

 

 

SQL> update aaa set a=10;

update aaa set a=10

*

第 1 行出现错误:

ORA-02291: 违反完整约束条件 (LIC.AAA_PRI) - 未找到父项关键字

 

SQL> create or replace trigger test_update_cascade

2 before

3 update on test

4 for each row

5 begin

6 update aaa set a=10;

7 end;

8 /

 

触发器已创建

 

SQL> commit;

 

提交完成。

 

SQL> update test set a=10;

 

已更新 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

A B C D E

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

10 b c d e

 

SQL> select * from aaa;

 

A B C D E

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

10 b c d e

3.4.2 关键字实现

SQL> drop trigger test_update_cascade;

 

触发器已删除。

 

SQL> alter table AAA drop constraint AAA_PRI;

 

表已更改。

 

SQL> COMMIT;

 

提交完成。

 

SQL> alter table AAA

2 add constraint AAA_PRI foreign key (A, B)

3 references TEST (A, B) deferrable initially deferred;

 

表已更改。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

A B C D E

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

10 b c d e

 

SQL> select * from aaa;

 

A B C D E

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

10 b c d e

 

SQL> update test set a=100;

 

已更新 1 行。

 

SQL> update aaa set a=100;

 

已更新 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

A B C D E

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

100 b c d e

 

SQL> select * from aaa;

 

A B C D E

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

100 b c d e