Database Tutorial

Course Tutorial Site

Site Admin

Describe Foreign Keys

with 2 comments

This page shows you how to lookup foreign key relationships in the database catalogs of the Oracle, MySQL, and SQL Server (soon) databases. It is useful when you’re troubleshooting a problem when you’ve implemented referential integrity in your database through database-level constraints.

Describe Foreign Keys
OracleMySQLSQL Server

Oracle

The following formatting instructions ensure clear output. You can also convert this query into a view by following the instructions from the CREATE statement page.

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;

MySQL

MySQL 5 introduced the information_schema, which typically all users have permission to access. You can discover the foreign keys and their respective foreign keys by using the following query.

MySQL doesn’t provide many formatting options, so I’d suggest you use the TEE and NOTEE commands to create a log file. If you need more on how to create a session log file, please check here. There under the drop down for Re-runnable Referential Integrity Scripts. That let’s you open the output in a text editor window that won’t cause ineffectual line wrapping.

SELECT   CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint"
,        CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key"
,        CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key"
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
WHERE    tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name
,        kcu.column_name;

SQL Server

… under development …

Written by michaelmclaughlin

January 17th, 2010 at 9:55 pm

Posted in

2 Responses to 'Describe Foreign Keys'

Subscribe to comments with RSS or TrackBack to 'Describe Foreign Keys'.

  1. There is the ‘c’ missing from instructions in the oracle section.

    Jacob

    6 May 11 at 5:45 pm

  2. Fixed.

    michaelmclaughlin

    22 Oct 14 at 2:33 am

Leave a Reply