I am trying to figure out what table/columns a constraint is tied to.
Alter table CITY add constraint FK_state
foreign_key (country_code, state_code)
references STATE (country_code, state_code);
I have built a query that tells me the table name, the name of the constraint, the columns of the constraint and the type of constraint (Check, Primary, Referential/foreign) from the primary table (in the example it would be CITY), but I cannot figure out how to determine the foreign table (in the example it would be the STATE table) that's referenced in the constraint.
Any suggestions would be appreciated.
You can make use of the ALL_CONSTRAINTS view
Life is a journey, not a destination!
You need to join the dba_constraints table back to
the dba_constraints table using r_constraint_name (version 8.0) to find the refering table_name.
Performance... Push the envelope!
Hi there smcdmc
I would use the following query to find that information:
A.TABLE_NAME "Parent Table",
A.CONSTRAINT_TYPE "Parent Type",
B.TABLE_NAME "Child Table",
B.CONSTRAINT_TYPE "Child Type"
FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
WHERE B.R_CONSTRAINT_NAME = A.CONSTRAINT_NAME
You could add to this any additional information that you wish to see.
Hope this helps
Thank you all. Your suggestions worked.
Click Here to Expand Forum to Full Width