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.
Oracle
The following formatting instrutions 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 …
