级联delete
首先建立一个主表txs_a:
- <span style="font-size:16px;">create table txs_a
- (
- id number(2) primary key,
- name varchar2(4)
- )
- </span>
然后创建一个从表txs_b:
- <span style="font-size:16px;">create table txs_b
- (
- a varchar2(4) primary key,
- b varchar2(4),
- id number(2),
- constraints txsb_id_fk foreign key (id) references TXS_a(id) on delete cascade
- )
- </span>
从表中的id为外键
向主表中插入数据:
- <span style="font-size:16px;">insert into txs_a values(4,'qq)
- insert into txs_a values(3,'ww')
- insert into txs_a values(2,'ee')
- insert into txs_a values(1,'rr')
- </span>
向从表中插入数据:
- <span style="font-size:16px;">insert into txs_b values(1,'pp',1)
- insert into txs_b values(2,'oo',2)
- insert into txs_b values(3,'ii',3)
- insert into txs_b values(4,'uu',4)
- </span>
测试:
- <span style="font-size:16px;">delete from txs_a where id=1
- </span>
查看从表中的id为1的行也被删除了
- <span style="font-size:16px;">create or replace trigger update_a_trriger
- after update of id on a
- for each row
- begin
- update b
- set b.id=:new.id
- where b.id=:old.id;
- end;
- </span>
级联Update
在oracle中不能像级联删除一样使用 on update cascade语句,如果需要级联更新就要创建触发器:
测试:
- <span style="font-size:16px;">update a set id=10 where id=1
- </span>
查看从表b中id为1的行id值同时变为10
注意:
当使用 alter tableb add constraints b_id_fk foreign key(id) references a(id) on delete cascade 语句添加外键时 从表b中的外键id值应该与主表a中的id值保持一致,否则oracle会提示“未找到父项关键字”!