Validating foreign keys
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.
Subscribe via RSS
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
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
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
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
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
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
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
Great Query, Thank you very much!
Jaliya
20 Apr 11 at 9:29 pm
Muy buena consulta…..!!!!
felicitaciones!
Javier Ramirez
4 Aug 11 at 8:03 am
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
[...] an SQL script to list all constraints in an Oracle database together with affected columns. Putting it here in [...]
Oracle: Finding all constraints and their affected columns
30 Jan 12 at 3:09 am
Excellent.. I really like it!
karan
29 Mar 12 at 10:22 pm
Excellent !!! Thanx a lot…
Vivek
8 Oct 12 at 2:36 am
Thanks for this. really useful one. Thanks it saves my manual work. Thanks a lot….
Balakumr S
31 Jan 13 at 5:30 am