关系表的级联删除(ON DELETE CASCADE的用法)



注明：分析的很好很透彻的一篇博客。转载自http://blog.chinaunix.net/uid-20124596-id-1734474.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