Viewing oracle's metadata about primary/foreign key constraints.

Question

Which table contains detailed information(For example the table the foreign key is referring to) about the constraints? The tables 'all_cons_columns' , 'all_constraints' contains only the name of the constraints which isn't very helpful. I am currently using dbms_metadata.get_ddl() but it doesn't work on all the databases.

Thanks.

Answer 1

It is all in there: the column R_CONSTRAINT_NAME in ALL_CONSTRAINTS contains the name of the referenced PK/UK constraint for a foreign key. You can then look up that constraint to get the TABLE_NAME of the reference table.

When looking at ALL_CONS_COLUMNS, the POSITION of the column in the foreign key will match the POSITION of the column in the primary/unique key.

Answer 2

Have a look at: Reverse Engineering a Data Model. Based on this I did a Python program that dumps Oracle db schema to text. There is PRIMARY_KEYS_INFO_SQL and FOREIGN_KEYS_INFO_SQL that do what you are interested in.

Answer 3

This statement lists tables, constraint names, and foreign key table names:

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