MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Validating foreign keys

with 19 comments

Somebody asked how to validate foreign key constraints in an Oracle database. The following query finds constraints, and displays the table and column that holds constraint with table and column name pointed to by the constraint. Since a foreign key to primary key relationship defines the list of values for a foreign key column, the values must be found in the primary key column.

Both user_constraints and user_cons_columns are catalog views that limit you to your own schema. The user_constraints view lets you find information about constraints, while the user_cons_columns view lets you see column level detail about the constraints.

The query lets you resolve where to look for those keys without manually inspecting table creation scripts.

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
ORDER BY ucc1.TABLE_NAME
,        uc.constraint_name;

You generate the following output when you run this query. The results shows you: (a) constraint names with their corresponding table and column names; and (b) table and column names that holds the primary key which is referenced by foreign keys.

Constraint Name:                       References
Table.Column                           Table.Column
-------------------------------------- --------------------------------
FK_ADDRESS_1                           REFERENCES
(ADDRESS.CONTACT_ID)                   (CONTACT.CONTACT_ID)
 
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_ADDRESS_3                           REFERENCES
(ADDRESS.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_ADDRESS_4                           REFERENCES
(ADDRESS.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_1                     REFERENCES
(COMMON_LOOKUP.CREATED_BY)             (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_2                     REFERENCES
(COMMON_LOOKUP.LAST_UPDATED_BY)        (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_1                           REFERENCES
(CONTACT.MEMBER_ID)                    (MEMBER.MEMBER_ID)
 
FK_CONTACT_2                           REFERENCES
(CONTACT.CONTACT_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_CONTACT_3                           REFERENCES
(CONTACT.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_4                           REFERENCES
(CONTACT.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)

You can then query the table and column referenced by the foreign key to determine the valid list of primary keys in the table. Extending the basic query design, you can narrow it to a specific constraint. This becomes very useful when you try to insert a row into an address table with an foreign key value that isn’t found in the list of valid primary keys.

A sample INSERT statement would be:

INSERT INTO address
VALUES
( 1101
, 1008
, 2001                                  -- This foreign key isn't a valid primary key.
,'Nowhereville'
,'Beatledom'
,'11111-1111'
, 3
, SYSDATE
, 3
, SYSDATE );

In my test instance, you would get a constraint violation error like the one below. You can download the setup scripts from McGraw-Hill’s web site for Oracle Database 11g PL/SQL Programming book that I wrote.

INSERT INTO address
*
ERROR at line 1:
ORA-02291: integrity CONSTRAINT (STUDENT.FK_ADDRESS_2) violated - parent KEY NOT found

Then, you can modify the earlier query to find the offending primary key column. Its offense is that there is no equivalent value to what you tried to input into another table. Here’s how you find the primary key column table:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      uc.constraint_type = 'R'
AND      uc.constraint_name = UPPER('&input_constraint_name');

It returns the following in my test instance:

CONSTRAINT Name:                       REFERENCES:
 TABLE.COLUMN                           TABLE.COLUMN
-------------------------------------- --------------------------------------
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)

You can now verify whether the value, 2001, that you tried to insert into a foreign key column exists. The query would be like follows:

SELECT   common_lookup_id
FROM     common_lookup
WHERE    common_lookup_id = 2001;

The query will say that no rows were found. You can remove the WHERE clause to find the list of valid primary key values.

Written by maclochlainn

March 5th, 2009 at 6:18 pm

Posted in Oracle,sql

19 Responses to 'Validating foreign keys'

Subscribe to comments with RSS or TrackBack to 'Validating foreign keys'.

  1. Sweet I’m adding this little script to my collection. Little scripts like this make me less dependent on Toad.

    Kevin Ashton

    11 Mar 09 at 11:47 pm

  2. Excellent query. I was looking for such but couldnt find the one written. 10 on 10.

    Regards
    Naresh

    Naresh

    25 Mar 09 at 5:27 am

  3. Hi,
    I think there is a bug in first query on page.
    Use this query to obtain foreign keys when first table is:
    —————————–
    – TabRoot
    —————————–
    – col1___PK
    – col2___PK
    —————————–

    and second one is:
    —————————–
    – TabDerived
    —————————–
    – ccol1___FK_of_TabRoot.col1
    – ccol2___FK_of_TabRoot.col2
    —————————–

    You will get that:
    TabDerived.ccol1 REFERENCES TabRoot.col1
    TabDerived.ccol1 REFERENCES TabRoot.col2
    TabDerived.ccol2 REFERENCES TabRoot.col1
    TabDerived.ccol2 REFERENCES TabRoot.col2

    To solve a problem add after 13 line of this query:
    AND ucc1.POSITION = ucc2.POSITION

    Piotr

    19 Oct 09 at 5:26 am

  4. First I want to thank you for writing this article, I’m using the query successfully!..but at this moment I’m trying to understand how to change it in case of multi-columns foreign keys…

    Ciao!:-)

    Fabio

    21 Oct 09 at 6:52 am

  5. Fabio, Piotr in Poland notice that too. I’d only put it out for surrogate keys. It’s fixed above now for both single and multiple column keys.

    maclochlainn

    21 Oct 09 at 9:33 am

  6. After hours of searching the web for something like this, your query is the only one that actually works. Thanks so much!

    Andre Kradolfer

    22 Jan 10 at 10:43 am

  7. I find this article very good.
    A question:
    how can I constraints between different schema retrieve?

    SELECT *
    FROM all_constraints uc
    WHERE uc.constraint_type = ‘R’
    and uc.owner!=uc.r_owner;

    simple with all_contraints und all_cons_columns?

    Follows query is right?:

    select *
    FROM all_constraints uc
    join
    all_cons_columns ucc1
    on uc.constraint_name = ucc1.constraint_name
    and uc.OWNER = ucc1.owner
    join
    all_cons_columns ucc2
    on uc.r_constraint_name = ucc2.constraint_name
    and uc.R_OWNER = ucc2.owner
    — Correction for multiple column primary keys.
    AND ucc1.position = ucc2.position
    WHERE 1=1
    AND uc.constraint_type = ‘R';

    Michele Paoli

    3 Mar 10 at 5:04 am

  8. Great Query, Thank you very much!

    Jaliya

    20 Apr 11 at 9:29 pm

  9. Muy buena consulta…..!!!!
    felicitaciones!

    Javier Ramirez

    4 Aug 11 at 8:03 am

  10. Thanks for the query. This helped me find all the foreign key constraints I was interested in.

    Mark

    26 Aug 11 at 12:00 pm

  11. [...] an SQL script to list all constraints in an Oracle database together with affected columns. Putting it here in [...]

  12. Excellent.. I really like it!

    karan

    29 Mar 12 at 10:22 pm

  13. Excellent !!! Thanx a lot…

    Vivek

    8 Oct 12 at 2:36 am

  14. Thanks for this. really useful one. Thanks it saves my manual work. Thanks a lot….

    Balakumr S

    31 Jan 13 at 5:30 am

  15. Thank u very much !

    yam_france

    5 Jul 13 at 3:27 am

  16. Thanks u saved my time.

    cs

    11 Jul 13 at 5:19 am

  17. very much useful.. and it saved lot of time.

    send2gmr

    15 Jul 13 at 3:52 am

  18. Superb query ..Awesome

    Rohan

    17 Jul 13 at 10:03 pm

  19. very useful query .. thnx for that ..

    piyush s

    25 Jul 14 at 1:58 am

Leave a Reply