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

«
»