Jump to content

Get all foreign key constraints


NotionCommotion

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. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.