Disecting a constraint

[smcdmc]

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.

[sambavan]

You can make use of the ALL_CONSTRAINTS view

Sam

[roobaron]

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.

[alison]

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

[smcdmc]

Thank you all. Your suggestions worked.