主外键关联删除(on delete set null和on delete cascade)
来源:互联网 发布:张子凡 陆林轩 知乎 编辑:程序博客网 时间:2024/06/07 10:54
主外键关联,当删除的是父表数据,参照这些要删除的数据,Oracle有三种处理方式:
1、禁止删除,也是Oracle默认方法。
2、将参照要删除数据的子表对应数据置空。
3、将参照要删除数据的子表对应数据删除。
对于1,比较容易理解,不解释。
对于2,需要使用on delete set null建立外键约束。实验:
create table dept_test
(deptno number(10) not null,
deptname varchar2(30) not null,
constraint pk_dept_test primary key(deptno));
(deptno number(10) not null,
deptname varchar2(30) not null,
constraint pk_dept_test primary key(deptno));
create table emp_test
(empno number(10) not null,
fname varchar2(20) ,
lname varchar2(20) ,
dept number(10) ,
constraint pk_emp_test primary key(empno));
(empno number(10) not null,
fname varchar2(20) ,
lname varchar2(20) ,
dept number(10) ,
constraint pk_emp_test primary key(empno));
alter table emp_test
add constraint fk_emp_dept_test foreign key(dept) references dept_test(deptno) on delete set null;
add constraint fk_emp_dept_test foreign key(dept) references dept_test(deptno) on delete set null;
insert into dept_test values(1,'销售部');
insert into dept_test values(2,'财务部');
insert into emp_test values (2,'Mary','Song',1);
insert into dept_test values(2,'财务部');
insert into emp_test values (2,'Mary','Song',1);
insert into emp_test values (3,'Linda','Liu',2);
insert into emp_test values (4,'Linlin','Zhang',1);
insert into emp_test values (4,'Linlin','Zhang',1);
delete from dept_test where deptno = 1;
1 row deleted.
SQL> select * from emp_test;
EMPNO FNAME LNAME DEPT
---------- -------------------- -------------------- ----------
2 Mary Song
3 Linda Liu 2
4 Linlin Zhang
SQL> select * from dept_test;
DEPTNO DEPTNAME
---------- ------------------------------
2 财务部
可以看到emp_test中参照dept_test中deptno=1的两条记录对应dept值已经置为空。EMPNO FNAME LNAME DEPT
---------- -------------------- -------------------- ----------
2 Mary Song
3 Linda Liu 2
4 Linlin Zhang
SQL> select * from dept_test;
DEPTNO DEPTNAME
---------- ------------------------------
2 财务部
SQL> truncate table emp_test;
Table truncated.
SQL> truncate table dept_test;
truncate table dept_test
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
此时也是禁止清空dept_test表,drop相同。Table truncated.
SQL> truncate table dept_test;
truncate table dept_test
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
如果想要删除父表,可以有两种方法:
1、先drop子表,再drop父表。
2、先删除约束alter table emp_test drop constraint fk_emp_dept_test;,再drop父表。
对于3,需要使用on delete cascade建立外键约束。实验:
alter table emp_test
add constraint fk_emp_dept_test foreign key(dept) references dept_test(deptno) on delete cascade;
add constraint fk_emp_dept_test foreign key(dept) references dept_test(deptno) on delete cascade;
delete from dept_test where deptno = 1;
1 row deleted.
SQL> select * from dept_test;
DEPTNO DEPTNAME
---------- ------------------------------
2 财务部
SQL> select * from emp_test;
EMPNO FNAME LNAME DEPT
---------- -------------------- -------------------- ----------
3 Linda Liu 2
DEPTNO DEPTNAME
---------- ------------------------------
2 财务部
SQL> select * from emp_test;
EMPNO FNAME LNAME DEPT
---------- -------------------- -------------------- ----------
3 Linda Liu 2
可以看到子表中参照父表的行也被删除了。
SQL> truncate table emp_test;
Table truncated.
SQL> truncate table dept_test;
truncate table dept_test
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
这种删除表的和上面相同。Table truncated.
SQL> truncate table dept_test;
truncate table dept_test
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
0 0
- 主外键关联删除(on delete set null和on delete cascade)
- on delete set null & on delete cascade
- on delete set null & on delete cascade
- On delete cascade和on delete set null
- on delete cascade和on delete set null的级联删除
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle 建立外键约束时on delete cascade 和 on delete set null
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- ON DELETE CASCADE(级联删除)
- 您想知道如何在Access 2003中使用ON UPDATE CASCADE/ON DELETE CASCADE,ON UPDATE SET NULL/ ON DELETE SET NULL吗?
- SQL Server 级联删除 ON DELETE CASCADE
- sql 联级删除 ON DELETE CASCADE
- 级联引用完整性约束ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
- 数据库级联更新 on update cascade和级联删除 on delete cascade
- on update cascade 和on delete cascade 的作用
- 小米染指平板电脑凶多吉少
- Flex 数组 Array 用法---gson转换为flex array的方法--两个Array()/as Array
- C#中判断空字符串的3种方法性能分析
- poj1417 并查集+背包
- C#优化字符串操作
- 主外键关联删除(on delete set null和on delete cascade)
- windows运行命令大全
- 上一篇讲到最简单的表单处理,就将输入的字符串输出了。
- 数据库连接类的写法
- C#的6种常用集合类大比拼
- json转换java和java转json的方法
- C#开发编码规范
- 一、前面简短的唠叨
- UVA-10340-All in All