在约束中查找父关键点

Question

我要破例了

ORA-02291:违反了完整性约束（ADS.ADS\u JOB\u FAMILIES\u F03）-未找到父密钥

我在正在插入的表中找到了约束。

如何查找父键和子键。。。（列）。

注意：我正在使用Oracle

Answer 1

对于父表：

select * from all_constraints 
         where constraint_name in (
               select R_CONSTRAINT_NAME 
                 from all_constraints 
                where constraint_name = 'ADS_JOB_FAMILIES_F03'); 

对于父列：

select * 
  from all_cons_columns 
 where constraint_name in (
       select constraint_name 
         from all_constraints 
        where constraint_name in (
              select R_CONSTRAINT_NAME 
                from all_constraints 
               where constraint_name = 'ADS_JOB_FAMILIES_F03')); 

这并不完全完美，因为它忽略了约束的所有者。但我认为它会对你有用。

编辑：我现在甚至找到了这个。这将给出如下查询：

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE a.constraint_name = 'ADS_JOB_FAMILIES_F03';