如何实现关系表的级联删除(ON DELETE CASCADE的用法)
来源:互联网 发布:淘宝客商品链接转换 编辑:程序博客网 时间:2024/06/08 00:44
如何实现关系表的级联删除(ON DELETE CASCADE的用法) 收藏
以下面两张表为例:
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.
转载处:http://blog.csdn.net/cn_chenfeng/archive/2005/11/06/523856.aspx
- 如何实现关系表的级联删除(ON DELETE CASCADE的用法)
- 如何实现关系表的级联删除(ON DELETE CASCADE的用法)
- 如何实现关系表的级联删除(ON DELETE CASCADE的用法)
- 如何实现关系表的级联删除(ON DELETE CASCADE的用法)
- 如何实现关系表的级联删除(ON DELETE CASCADE的用法)
- 关系表的级联删除(ON DELETE CASCADE的用法)
- ON DELETE CASCADE(级联删除)
- on delete cascade和on delete set null的级联删除
- SQL Server 级联删除 ON DELETE CASCADE
- delete cascade 级联删除
- 数据库级联更新 on update cascade和级联删除 on delete cascade
- T-SQL级联删除——ON DELETE CASCADE
- 数据库_级联更新/级联删除_ON UPDATE CASCADE/ON DELETE CASCADE
- SQL SERVER 主键和外键中的 级联删除(ON DELETE CASCADE)和级联更新(ON UPDATE CASCADE)
- SQL SERVER 主键和外键中的 级联删除(ON DELETE CASCADE)和级联更新(ON UPDATE CASCADE)
- on update cascade 和on delete cascade 的作用
- on update cascade 和on delete cascade 的区别
- mysql级联删除-----Mysql 的 Cascade Restrict
- NHibernate学习心得二
- C游戏开发中遇到的一些问题
- ant教程
- 小型外包软件公司最有帮助的在线协同工具
- 智能指针auto_prt的使用(c++学习笔记)
- 如何实现关系表的级联删除(ON DELETE CASCADE的用法)
- 裁员纪实:联想不是我的家(转)
- Temporary Tables临时表
- Temporary Tables
- coldfusion中使用jQuery
- Checkpoint和SCN的解析
- 第 1 部分: 类和类装入
- oracle 表级锁(TM锁)
- rpm包下载网址收集