Oracle创建外键可选用参数说明
(一)、概叙
oracle从9i开始,在主表操作更新删除时,对子表参照数据处理方式说明:
1:使用默认的创建方式,在删除被参照的数据时,将无法被删除;
2:on delete cascade关键字来决定在删除被参照数据时是否要将参照这个数据的那些数据一并删除,即所谓的级联删除;
3:on delete set null关键字来决定是将那些参照这条数据的数据的对应值赋空。
4:deferrable initially deferred关键字主要作用是在使用更新操作时,延期外键检测;以此来保证更新操作成功;如果没有此关键字,则需在主表内添加触发器来完成更新操作。
(二)、实例表创建
2.1:创建父表TEST
SQL>create table TEST(
2A NUMBER not null,
3B VARCHAR2(10) not null,
4C VARCHAR2(10),
5D VARCHAR2(10),
6E VARCHAR2(10)
7);
表已创建。
SQL> alter table TEST add constraint TEST_PRI primary key (A, B);
表已修改。
2.2:创建TEST对应子表AAA
SQL>create table AAA(
2A NUMBER,
3B VARCHAR2(10),
4C VARCHAR2(10),
5D VARCHAR2(10),
6E VARCHAR2(10)
7);
表已创建。
(三)、分类讨论
3.1默认情况下,语句如下外键
SQL> alter table AAA add constraint AAA_PRI foreign key (A, B)
2references 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
2add constraint AAA_PRI foreign key (A, B)
3references 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
2add constraint AAA_PRI foreign key (A, B)
3references 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 BCDE
---------- ---------- ---------- ---------- ----------
cde
1 bcde
SQL> delete from test;
已删除1行。
SQL> select * from aaa;
A BCDE
---------- ---------- ---------- ---------- ----------
cde
cde
SQL> select * from test;
未选定行
3.4使用deferrable initially deferred关键字创建外键
3.4.1触发器实现
SQL> alter table AAA
2add constraint AAA_PRI foreign key (A, B)
3references 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 BCDE
---------- ---------- ---------- ---------- ----------
1 bcde
SQL> select * from aaa;
A BCDE
---------- ---------- ---------- ---------- ----------
1 bcde
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
2before
3update on test
4for each row
5begin
6update aaa set a=10;
7end;
8/
触发器已创建
SQL> commit;
提交完成。
SQL> update test set a=10;
已更新1行。
SQL> commit;
提交完成。
SQL> select * from test;
A BCDE
---------- ---------- ---------- ---------- ----------
10 bcde
SQL> select * from aaa;
A BCDE
---------- ---------- ---------- ---------- ----------
10 bcde
3.4.2关键字实现
SQL> drop trigger test_update_cascade;
触发器已删除。
SQL> alter table AAA drop constraint AAA_PRI;
表已更改。
SQL> COMMIT;
提交完成。
SQL> alter table AAA
2add constraint AAA_PRI foreign key (A, B)
3references TEST (A, B) deferrable initially deferred;
表已更改。
SQL> commit;
提交完成。
SQL> select * from test;
A BCDE
---------- ---------- ---------- ---------- ----------
10 bcde
SQL> select * from aaa;
A BCDE
---------- ---------- ---------- ---------- ----------
10 bcde
SQL> update test set a=100;
已更新1行。
SQL> update aaa set a=100;
已更新1行。
SQL> commit;
提交完成。
SQL> select * from test;
A BCDE
---------- ---------- ---------- ---------- ----------
100 bcde
SQL> select * from aaa;
A BCDE
---------- ---------- ---------- ---------- ----------
100 bcde[@more@]