常用sql----实现关联表的数据级联更新

最近对多个表要进行级联更新主键id，但又不知道主外键关联关系，所以打了草稿

 

 

--搜索表间 的关系--主外键
SELECT   a.table_name,a.constraint_name,b.column_name,p.table_name
  from   user_constraints   a,   user_cons_columns   b ,user_constraints   p
  where   --a.table_name   =   'your_table '   and   
       a.constraint_type= 'R' 
      and   a.constraint_name=b.constraint_name
      and   a.r_constraint_name = p.constraint_name
      
--主外键     
 select table_name as 表名,constraint_name from user_constraints where constraint_type='R';


--删除外键
alter table bird  drop CONSTRAINT fk_cat_id;


--更新主表
create table tem_bird
as
select rownum as newid,id as old_id from bird;
update bird c 
set c.id=(select tm.newid from tem_bird tm where c.id=tm.old_id);


--创建子表临时表
create table tem_cat
as
select rownum as newid,id as old_id from cat;


--更新子表
update cat c 
set c.id=(select tm.newid from tem_cat tm where c.id=tm.old_id);


--更新主表
update bird c 
set c.catid=(select tm.newid from tem_cat tm where c.catid=tm.old_id);
commit;


--建立关联外键
alter table  bird add  CONSTRAINT fk_cat_id FOREIGN KEY (catid) REFERENCES cat (ID) ENABLE ;


drop table tem_cat;
drop table tem_bird;