Oracle查询表之间的主外键关系
一、在已经有的项目系统开发,如果数据库设计,表与表之间建立了外键约束,那么删除时就要去删对应约束列
1、查询对于应表中的主键是哪些表的外键,以test表为例
select c.TABLE_NAME from all_constraints p, all_constraints c where p.table_name = upper('test') and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') and c.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') and c.constraint_type = 'R' and p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME;
2、查询对于应表中的外键来源于哪些表,以test表为例
select cl.table_name from user_cons_columns cl left join user_constraints c on cl.constraint_name = c.r_constraint_name where c.constraint_type = 'R' and c.table_name = upper('test');
yexiangyang
moyyexy@gmail.com