如何实现关系表的级联删除(ON DELETE CASCADE的用法)

转自：http://hi.baidu.com/pingfandexiake/blog/item/b74c0e3631f2f3daa3cc2bcf.html

以下面两张表为例：

SQL> desc person
名称 是否为空? 类型
----------------------------------------- -------- ------------------------

PERSONID NOT NULL NUMBER(9)
PROFESSION NUMBER(2)
IDORPASSPORTNO VARCHAR2(18)
INSURANCE VARCHAR2(32)
FIRSTNAME VARCHAR2(32)
LASTNAME VARCHAR2(32)
CHINESENAME VARCHAR2(64)
SEX CHAR(1)
BIRTHDAY DATE
USERTYPE VARCHAR2(2)
SEARCHCODE CHAR(10)
ORGID NUMBER(9)
ADDRESSID NUMBER(9)

SQL> desc PERSONPROFESSION
名称 是否为空? 类型
----------------------------------------- -------- ------------------------

PROFESSIONID NOT NULL NUMBER(2)
NAME VARCHAR2(32)
ALIAS VARCHAR2(32)
INITCREDIT NUMBER(3)
MEMO VARCHAR2(200)

假定PERSONPROFESSION为父表，PERSON为子表，建立如下约束条件：

SQL>ALTER TABLE PERSONPROFESSION ADD (CONSTRAINT PK_PERSONPROFESSION PRIMARY KEY (PROFESSIONID) USING INDEX TABLESPACE WACOS;

表已更改。

SQL>ALTER TABLE PERSON ADD (CONSTRAINT PK_PERSON PRIMARY KEY (PERSONID) USING INDEX TABLESPACE WACOS;

表已更改。

增加PERSON表的外键约束：

SQL>ALTER TABLE PERSON ADD (CONSTRAINT FK_PERSON1 FOREIGN KEY (PROFESSION)
REFERENCES PERSONPROFESSION (PROFESSIONID) );

表已更改。

分别向两张表里插入一行数据：

SQL>insert into PERSONPROFESSION values('1','sdfd','df','342','dfs');

已创建 1 行。

SQL> commit;

提交完成。

SQL>insert into person values('10','1','dsf','d','d','df','df','0','','sd','s','11','11');

已创建 1 行。

SQL> commit;

SQL> select PERSONID,PROFESSION from person;

PERSONID PROFESSION
---------- ----------
10 1

SQL> select PROFESSIONID,name from PERSONPROFESSION;

PROFESSIONID NAME
------------ --------------------------------
1 sdfd

SQL> delete from PERSONPROFESSION;
delete from PERSONPROFESSION
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (WACOS.FK_PERSON1) - 已找到子记录日志.

报错是因为父表里存在子表的记录，应先删除子表记录，再删除父表记录：

SQL> delete from PERSON;

已删除 1 行。

SQL> delete from PERSONPROFESSION;

已删除 1 行。

SQL> commit;

提交完成。

那么，如何实现两张表的级连删除呢？

重新建立PERSON的外键，加上ON DELETE CASCADE选项试一下：

SQL> alter table person drop CONSTRAINT FK_PERSON1;

表已更改。

SQL>ALTER TABLE PERSON ADD (CONSTRAINT FK_PERSON1 FOREIGN KEY (PROFESSION)
REFERENCES PERSONPROFESSION (PROFESSIONID) ON DELETE CASCADE);

表已更改。

增加外键约束的时候加ON DELETE CASCADE选项目的是可以在子表记录存在的情况下直接删除父表记录，而不用受约束的限制：
SQL> delete from PERSONPROFESSION;

已删除 1 行。

SQL> select PERSONID,PROFESSION from person;

PERSONID PROFESSION
---------- ----------
10 1

SQL> select * from PERSONPROFESSION;

未选定行

以上可以看出父表记录删除了，子表记录还存在。

那么在delete语句的末尾加上CASCADE，就可以实现两张表的级删除：

SQL> delete from PERSONPROFESSION cascade;

已删除 1 行。

SQL> select * from person;

未选定行

SQL> select * from PERSONPROFESSION;

未选定行

以下是ON DELETE的官方解释：

ON DELETE
The ON DELETE clause indicates that when a DELETE is executed on a referenced row in the referenced table, one of the following actions will be executed upon the constrained column, as specified by action:

NO ACTION(default)
The NO ACTION clause produces an error if the reference is violated. This is the default if action is not specified.

CASCADE
The CASCADE keyword removes all rows which reference the deleted row. Exercise caution with this action.

SET NULL
The SET NULL clause assigns a NULL value to all referenced column values