Disecting a constraint
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Disecting a constraint

  1. #1
    Join Date
    Feb 2001
    Posts
    27
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You can make use of the ALL_CONSTRAINTS view

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Aug 2001
    Posts
    111
    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!

  4. #4
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  5. #5
    Join Date
    Feb 2001
    Posts
    27
    Thank you all. Your suggestions worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width