Jump to content

Get all foreign key constraints


NotionCommotion
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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=#
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.