NotionCommotion Posted April 10, 2021 Share Posted April 10, 2021 I am trying to get a list of all foreign key constraints and executed the following: SELECT kcu.table_name, kcu.column_name, rel_tco.table_name fk_table_name, ccu.column_name AS fk_column_name, tc.constraint_name FROM information_schema.table_constraints tc INNER JOIN information_schema.key_column_usage kcu ON tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name INNER JOIN information_schema.constraint_column_usage AS ccu ON ccu.table_schema = tc.table_schema AND ccu.constraint_name = tc.constraint_name INNER JOIN information_schema.referential_constraints rco ON tc.constraint_schema = rco.constraint_schema AND tc.constraint_name = rco.constraint_name INNER JOIN information_schema.table_constraints rel_tco ON rco.unique_constraint_schema = rel_tco.constraint_schema AND rco.unique_constraint_name = rel_tco.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public' ORDER BY kcu.table_name, rel_tco.table_name; It is almost what I want but I am getting duplicates for tables with composite keys. table_name | column_name | fk_table_name | fk_column_name | constraint_name -------------------------------+--------------------------+-----------------------+----------------+------------------------------------ ... upload | guid | guid | guid | fk_17bde61f2b6fcfb2 upload | type | upload_type | mime | fk_17bde61f8cde57291d3960242858050 upload | mime | upload_type | type | fk_17bde61f8cde57291d3960242858050 upload | mime | upload_type | ext | fk_17bde61f8cde57291d3960242858050 upload | mime | upload_type | mime | fk_17bde61f8cde57291d3960242858050 upload | ext | upload_type | type | fk_17bde61f8cde57291d3960242858050 upload | ext | upload_type | ext | fk_17bde61f8cde57291d3960242858050 upload | ext | upload_type | mime | fk_17bde61f8cde57291d3960242858050 upload | type | upload_type | type | fk_17bde61f8cde57291d3960242858050 upload | type | upload_type | ext | fk_17bde61f8cde57291d3960242858050 ... What I would like to receive is this: table_name | column_name | fk_table_name | fk_column_name | constraint_name -------------------------------+--------------------------+-----------------------+----------------+------------------------------------ ... upload | guid | guid | guid | fk_17bde61f2b6fcfb2 upload | mime | upload_type | mime | fk_17bde61f8cde57291d3960242858050 upload | ext | upload_type | ext | fk_17bde61f8cde57291d3960242858050 upload | type | upload_type | type | fk_17bde61f8cde57291d3960242858050 ... Any recommendations? Thank you Quote Link to comment https://forums.phpfreaks.com/topic/312449-get-all-foreign-key-constraints/ Share on other sites More sharing options...
Barand Posted April 10, 2021 Share Posted April 10, 2021 Perhaps SELECT u.referenced_table_name , group_concat(u.referenced_column_name separator ', ') as ref_col_name , u.table_name , group_concat(u.column_name separator ', ') as col_name , u.constraint_name FROM information_schema.key_column_usage u JOIN information_schema.table_constraints c USING (constraint_schema,constraint_name) WHERE c.constraint_schema = ? AND c.constraint_type = 'FOREIGN KEY' GROUP BY u.constraint_name ORDER BY referenced_table_name, referenced_column_name Quote Link to comment https://forums.phpfreaks.com/topic/312449-get-all-foreign-key-constraints/#findComment-1585707 Share on other sites More sharing options...
NotionCommotion Posted April 10, 2021 Author Share Posted April 10, 2021 Good morning Barand, I substituted "separator" with a comma and removed the prepared statement WHERE clause for constraint_schema, however, then found that information_schema.key_column_usage.referenced_column_name doesn't exist for me. Ah, just noticed your "Perhaps". Oh well, appreciate your response regardless. Quote Link to comment https://forums.phpfreaks.com/topic/312449-get-all-foreign-key-constraints/#findComment-1585708 Share on other sites More sharing options...
Barand Posted April 10, 2021 Share Posted April 10, 2021 This is how my table looks... What have you got then? Quote Link to comment https://forums.phpfreaks.com/topic/312449-get-all-foreign-key-constraints/#findComment-1585709 Share on other sites More sharing options...
NotionCommotion Posted April 10, 2021 Author Share Posted April 10, 2021 43 minutes ago, Barand said: What have you got then? Using postgresql, not mysql. michael=# \d information_schema.key_column_usage View "information_schema.key_column_usage" Column | Type | Collation | Nullable | Default -------------------------------+------------------------------------+-----------+----------+--------- constraint_catalog | information_schema.sql_identifier | | | constraint_schema | information_schema.sql_identifier | | | constraint_name | information_schema.sql_identifier | | | table_catalog | information_schema.sql_identifier | | | table_schema | information_schema.sql_identifier | | | table_name | information_schema.sql_identifier | | | column_name | information_schema.sql_identifier | | | ordinal_position | information_schema.cardinal_number | | | position_in_unique_constraint | information_schema.cardinal_number | | | michael=# \d information_schema.table_constraints View "information_schema.table_constraints" Column | Type | Collation | Nullable | Default --------------------+-----------------------------------+-----------+----------+--------- constraint_catalog | information_schema.sql_identifier | | | constraint_schema | information_schema.sql_identifier | | | constraint_name | information_schema.sql_identifier | | | table_catalog | information_schema.sql_identifier | | | table_schema | information_schema.sql_identifier | | | table_name | information_schema.sql_identifier | | | constraint_type | information_schema.character_data | | | is_deferrable | information_schema.yes_or_no | | | initially_deferred | information_schema.yes_or_no | | | enforced | information_schema.yes_or_no | | | michael=# \d information_schema.constraint_column_usage View "information_schema.constraint_column_usage" Column | Type | Collation | Nullable | Default --------------------+-----------------------------------+-----------+----------+--------- table_catalog | information_schema.sql_identifier | | | table_schema | information_schema.sql_identifier | | | table_name | information_schema.sql_identifier | | | column_name | information_schema.sql_identifier | | | constraint_catalog | information_schema.sql_identifier | | | constraint_schema | information_schema.sql_identifier | | | constraint_name | information_schema.sql_identifier | | | michael=# \d information_schema.referential_constraints View "information_schema.referential_constraints" Column | Type | Collation | Nullable | Default ---------------------------+-----------------------------------+-----------+----------+--------- constraint_catalog | information_schema.sql_identifier | | | constraint_schema | information_schema.sql_identifier | | | constraint_name | information_schema.sql_identifier | | | unique_constraint_catalog | information_schema.sql_identifier | | | unique_constraint_schema | information_schema.sql_identifier | | | unique_constraint_name | information_schema.sql_identifier | | | match_option | information_schema.character_data | | | update_rule | information_schema.character_data | | | delete_rule | information_schema.character_data | | | michael=# Quote Link to comment https://forums.phpfreaks.com/topic/312449-get-all-foreign-key-constraints/#findComment-1585710 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.