Oracle中主从多表删除数据时,必须用级联删除吗?
一个主表,带了三个从表,一一关联,A为主表
A->B->C->D
从A表中删除一条数据时,要把BCD表里相关的数据都删除的话
用什么方法最好,必须用级联删除吗?
能不能给个实例。谢谢!!!!!!!!
一个主表,带了三个从表,一一关联,A为主表
A->B->C->D
从A表中删除一条数据时,要把BCD表里相关的数据都删除的话
用什么方法最好,必须用级联删除吗?
能不能给个实例。谢谢!!!!!!!!
create table b (col number references a(col) on delete cascade);
后面的C表D表类似处理。
--做一个例子测试一下:SQL> create table table3(id number(8) primary key,name varchar2(8));Table createdSQL> insert into table3 values(1,'张三');1 row insertedSQL> insert into table3 values(2,'李四');1 row insertedSQL> commit;Commit completeSQL> create table table4(id2 number(6) primary key,id number(8),score number(8),
2 constraint FK_id foreign key (id) references table3(id));Table createdSQL> insert into table4 values(10,1,3);1 row insertedSQL> insert into table4 values(11,2,2008);1 row insertedSQL> commit;Commit completeSQL> alter table table3 drop primary key cascade;Table alteredSQL> select * from table3; ID NAME
--------- --------
1 张三
2 李四SQL> select * from table4; ID2 ID SCORE
------- --------- ---------
10 1 3
11 2 2008SQL> delete from table3 where id=1;1 row deletedSQL> commit;Commit completeSQL> select * from table3; ID NAME
--------- --------
2 李四SQL> select * from table4; ID2 ID SCORE
------- --------- ---------
10 1 3
11 2 2008SQL>
--从上述可以看出:删除table3的主关键字id=1时,cascade并不能删除table4表中的id=1的记录。
--所以希望楼主还是使用触发器的联级触发吧!不过调试时要思路清晰,多做测试。
但是触发器消耗资源较多, 而且有经验的DBA可能会知道,解发器会引起许多麻烦, 不建议使用外键多级级联删除我测试是完全可以做到的:
SQL> create table t1
2 (
3 id varchar2(10),
4 name varchar2(10)
5 );
Table created
SQL> create table t2
2 (
3 id varchar2(10),
4 pid varchar2(10),
5 name varchar2(10)
6 );
Table created
SQL>
SQL> create table t3
2 (
3 id varchar2(10),
4 pid varchar2(10),
5 name varchar2(10)
6 );
Table createdSQL> alter table t1
2 add constraint pk_t1 primary key (id);
Table alteredSQL>
SQL> alter table t2
2 add constraint pk_t2 primary key (id);
Table altered
SQL>
SQL> alter table t3
2 add constraint pk_t3 primary key (id);
Table alteredSQL> alter table t2 add constraint FK_t1_t2 foreign key (pid) references t1(id) on delete cascade not deferrable;
Table alteredSQL> alter table t3 add constraint FK_t2_t3 foreign key (pid) references t2(id) on delete cascade not deferrable;
Table alteredSQL> insert into t1 values('1','aaa');
1 row inserted
SQL> insert into t1 values('2','bbb');
1 row inserted
SQL> insert into t2 values ('1','1','mmm');
1 row inserted
SQL> insert into t2 values ('2','1','nnn');
1 row inserted
SQL> insert into t3 values ('1','1','xxx');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t1;
ID NAME
---------- ----------
1 aaa
2 bbb
SQL> select * from t2;
ID PID NAME
---------- ---------- ----------
1 1 mmm
2 1 nnn
SQL> select * from t3;
ID PID NAME
---------- ---------- ----------
1 1 xxxSQL> delete t1 where id = '1';
1 row deleted
SQL> commit;
Commit complete
SQL> select * from t1;
ID NAME
---------- ----------
2 bbb
SQL> select * from t2;
ID PID NAME
---------- ---------- ----------
SQL> select * from t3;
ID PID NAME
---------- ---------- ----------
SQL> rollback;
Rollback complete