snippetModerate
How to retrieve foreign key constraints data
Viewed 0 times
constraintsforeignretrievehowdatakey
Problem
I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.
I've found this, but does not gives all the info I need:
https://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists
I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it :)
I've found this, but does not gives all the info I need:
https://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists
I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it :)
Solution
After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:
SELECT
CONS.CONSTRAINT_NAME,
CONS.TABLE_NAME,
COLS.COLUMN_NAME,
CONS.R_CONSTRAINT_NAME,
CONS_R.TABLE_NAME R_TABLE_NAME,
COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAMECode Snippets
SELECT
CONS.CONSTRAINT_NAME,
CONS.TABLE_NAME,
COLS.COLUMN_NAME,
CONS.R_CONSTRAINT_NAME,
CONS_R.TABLE_NAME R_TABLE_NAME,
COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAMEContext
StackExchange Database Administrators Q#11047, answer score: 16
Revisions (0)
No revisions yet.