查询主表的主键被其他表作为外键参照的SQL

1,查询主表的主键被其他表作为外键参照的SQL

select child_cons.constraint_name,

child_cons.table_name child_table_name,

parent_cons.table_name parent_table,

child_cons.status

from (select *

from all_constraints

where owner = SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’)

and table_name = ‘&parent_tab’) parent_cons,

(select *

from all_constraints

where owner = SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’)

and constraint_type = ‘R’) child_cons

where parent_cons.constraint_name = child_cons.r_constraint_name;

2,Disable 其他表的外键约束

select 'alter table ’ || child_cons.table_name || ’ disable constraint ’ ||

child_cons.constraint_name || ‘;’

from (select *

from all_constraints

where owner = SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’)

and table_name = ‘&parent_tab’) parent_cons,

(select *

from all_constraints

where owner = SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’)

and constraint_type = ‘R’) child_cons

where parent_cons.constraint_name = child_cons.r_constraint_name;