View oracle metadata about primary / foreign key constraints

Question

Which table contains detailed information (for example, the table referenced by the foreign key) about the restrictions? The tables "all_cons_columns", "all_constraints" contain only the name of the constraints, which is not very useful. I am currently using dbms_metadata.get_ddl (), but it does not work in all databases.

Thank.

Answer 1

That's it: the R_CONSTRAINT_NAME column in ALL_CONSTRAINTS contains the name of the associated PK / UK constraint for the foreign key. You can then find this restriction to get the TABLE_NAME reference table.

ALL_CONS_COLUMNS POSITION / .

Answer 2

, :

select c.table_name,c.constraint_name,  --c.r_constraint_name, 
  cc.table_name
from all_constraints c
inner join all_constraints cc on c.r_constraint_name = cc.constraint_name

Answer 3

script, , :

SELECT 
   'ALTER TABLE ' || a.table_name || ' ADD CONSTRAINT ' || a.constraint_name 
   || ' FOREIGN KEY (' || a.column_name || ') REFERENCES ' || jcol.table_name 
   || ' (' || jcol.column_name || ');' as commandforeign
FROM
   (SELECT 
       uc.table_name, uc.constraint_name, uc.r_constraint_name, col.column_name
    FROM 
       USER_CONSTRAINTS uc, USER_CONS_COLUMNS col
    WHERE 
       uc.constraint_type='R' and uc.constraint_name=col.constraint_name) a
 INNER JOIN 
    USER_CONS_COLUMNS jcol
 ON 
    a.r_constraint_name=jcol.constraint_name;

Answer 4

: . , Python, db Oracle . PRIMARY_KEYS_INFO_SQL FOREIGN_KEYS_INFO_SQL, , .