-
I am trying to figure out what table/columns a constraint is tied to.
Example.
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.
Thanks.
-
You can make use of the ALL_CONSTRAINTS view
Sam
-
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.
-
Hi there smcdmc
I would use the following query to find that information:
SELECT
A.TABLE_NAME "Parent Table",
A.CONSTRAINT_NAME pk,
A.CONSTRAINT_TYPE "Parent Type",
B.TABLE_NAME "Child Table",
B.CONSTRAINT_NAME fk,
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
Alison
-
Thank you all. Your suggestions worked.