Postgres SQL to list table foreign keys

Question

Is there a way using SQL to list all foreign keys for a given table  I know the table name   schema and I can plug that in

User · Answer

Ollyc s answer is good as it is not Postgres-specific  however  it breaks down when the foreign key references more than one column  The following query works for arbitrary number of columns but it relies heavily on Postgres extensions   select      att2 attname as  child column        cl relname as  parent table        att attname as  parent column       conname from     select          unnest con1 conkey  as  parent            unnest con1 confkey  as  child            con1 confrelid           con1 conrelid          con1 conname     from          pg class cl         join pg namespace ns on cl relnamespace   ns oid         join pg constraint con1 on con1 conrelid   cl oid     where         cl relname    child table          and ns nspname    child schema          and con1 contype    f       con    join pg attribute att on        att attrelid   con confrelid and att attnum   con child    join pg class cl on        cl oid   con confrelid    join pg attribute att2 on        att2 attrelid   con conrelid and att2 attnum   con parent

User · Answer

short but sweet  upvote if it works for you   select    from information schema key column usage where constraint catalog current catalog and table name  your table name  and position in unique constraint notnull

User · Answer

SELECT r conname        ct table name        pg catalog pg get constraintdef r oid  true  as condef   FROM pg catalog pg constraint r  information schema constraint table usage ct  WHERE r contype    f      AND r conname   ct constraint name  ORDER BY 1

User · Answer

You can use the PostgreSQL system catalogs   Maybe you can query pg constraint to ask for foreign keys  You can also use the Information Schema

User · Answer

psql does this  and if you start psql with   psql -E   it will show you exactly what query is executed  In the case of finding foreign keys  it s   SELECT conname    pg catalog pg get constraintdef r oid  true  as condef FROM pg catalog pg constraint r WHERE r conrelid    16485  AND r contype    f  ORDER BY 1   In this case  16485 is the oid of the table I m looking at - you can get that one by just casting your tablename to regclass like   WHERE r conrelid    mytable   regclass   Schema-qualify the table name if it s not unique  or the first in your search path    WHERE r conrelid    myschema mytable   regclass

User · Answer

None of the existing answers gave me results in the form that I actually wanted them in   So here is my  gargantuan  query for finding information about foreign keys   A few notes    The expressions used to generate from cols and to cols could be vastly simplified on Postgres 9 4 and later using WITH ORDINALITY rather than the window-function-using hackery I m using  Those same expressions are relying on the query planner not altering the returned order of results from UNNEST   I don t think it will  but I don t have any multiple-column foreign keys in my dataset to test with   Adding the 9 4 niceties eliminates this possibility altogether  The query itself requires Postgres 9 0 or later  8 x didn t allow ORDER BY in aggregate functions  Replace STRING AGG with ARRAY AGG if you want an array of columns rather than a comma-separated string    -  SELECT     c conname AS constraint name       SELECT n nspname FROM pg namespace AS n WHERE n oid c connamespace  AS constraint schema       tf name AS from table                SELECT STRING AGG QUOTE IDENT a attname        ORDER BY t seq          FROM                               SELECT                     ROW NUMBER   OVER  ROWS UNBOUNDED PRECEDING  AS seq                      attnum                 FROM                     UNNEST c conkey  AS t attnum                AS t             INNER JOIN pg attribute AS a ON a attrelid c conrelid AND a attnum t attnum       AS from cols       tt name AS to table                SELECT STRING AGG QUOTE IDENT a attname        ORDER BY t seq          FROM                               SELECT                     ROW NUMBER   OVER  ROWS UNBOUNDED PRECEDING  AS seq                      attnum                 FROM                     UNNEST c confkey  AS t attnum                AS t             INNER JOIN pg attribute AS a ON a attrelid c confrelid AND a attnum t attnum       AS to cols       CASE confupdtype WHEN  r  THEN  restrict  WHEN  c  THEN  cascade  WHEN  n  THEN  set null  WHEN  d  THEN  set default  WHEN  a  THEN  no action  ELSE NULL END AS on update      CASE confdeltype WHEN  r  THEN  restrict  WHEN  c  THEN  cascade  WHEN  n  THEN  set null  WHEN  d  THEN  set default  WHEN  a  THEN  no action  ELSE NULL END AS on delete      CASE confmatchtype  text WHEN  f  THEN  full  WHEN  p  THEN  partial  WHEN  u  THEN  simple  WHEN  s  THEN  simple  ELSE NULL END AS match type   -- In earlier postgres docs  simple was  u nspecified  but current versions use  s imple   text cast is required       pg catalog pg get constraintdef c oid  true  as condef FROM     pg catalog pg constraint AS c     INNER JOIN           SELECT pg class oid  QUOTE IDENT pg namespace nspname            QUOTE IDENT pg class relname  AS name         FROM pg class INNER JOIN pg namespace ON pg class relnamespace pg namespace oid       AS tf ON tf oid c conrelid     INNER JOIN           SELECT pg class oid  QUOTE IDENT pg namespace nspname            QUOTE IDENT pg class relname  AS name         FROM pg class INNER JOIN pg namespace ON pg class relnamespace pg namespace oid       AS tt ON tt oid c confrelid WHERE c contype    f  ORDER BY 1

User · Answer

check the ff post for your solution and don t forget to mark this when you fine this helpful  http   errorbank blogspot com 2011 03 list-all-foreign-keys-references-for html  SELECT   o conname AS constraint name     SELECT nspname FROM pg namespace WHERE oid m relnamespace  AS source schema    m relname AS source table     SELECT a attname FROM pg attribute a WHERE a attrelid   m oid AND a attnum   o conkey 1  AND a attisdropped   false  AS source column     SELECT nspname FROM pg namespace WHERE oid f relnamespace  AS target schema    f relname AS target table     SELECT a attname FROM pg attribute a WHERE a attrelid   f oid AND a attnum   o confkey 1  AND a attisdropped   false  AS target column FROM   pg constraint o LEFT JOIN pg class f ON f oid   o confrelid LEFT JOIN pg class m ON m oid   o conrelid WHERE   o contype    f  AND o conrelid IN  SELECT oid FROM pg class c WHERE c relkind    r

User · Answer

This is what I m currently using  it will list a table and it s fkey constraints  remove table clause and it will list all tables in current catalog     SELECT      current schema   AS  schema       current catalog AS  database        pg constraint  conrelid  regclass  text AS  primary table name        pg constraint  confrelid  regclass  text AS  foreign table name                  string to array                                string to array                      pg get constraintdef  pg constraint  oid                                                           2                                   1  AS  foreign column name         pg constraint  conindid  regclass  text AS  constraint name        TRIM           string to array              pg get constraintdef  pg constraint  oid                                   1   AS  constraint type        pg get constraintdef  pg constraint  oid  AS  constraint definition   FROM pg constraint AS  pg constraint   JOIN pg namespace AS  pg namespace  ON  pg namespace  oid    pg constraint  connamespace  WHERE     --fkey and pkey constraints      pg constraint  contype IN    f    p        AND      pg namespace  nspname   current schema       AND      pg constraint  conrelid  regclass  text IN   whatever table name

User · Answer

Extension to ollyc recipe    CREATE VIEW foreign keys view AS SELECT     tc table name  kcu column name      ccu table name AS foreign table name      ccu column name AS foreign column name FROM     information schema table constraints AS tc     JOIN information schema key column usage          AS kcu ON tc constraint name   kcu constraint name     JOIN information schema constraint column usage          AS ccu ON ccu constraint name   tc constraint name WHERE constraint type    FOREIGN KEY     Then   SELECT   FROM foreign keys view WHERE table name  YourTableNameHere

User · Answer

SELECT      conrelid  regclass AS table from      conname      pg get constraintdef oid  as condef         FROM pg catalog pg constraint r  will also work for all constraints  E g  with pysql

User · Answer

Proper solution to the problem  using information schema  working with multi column keys  joining columns of different names in both tables correctly and also compatible with ms sqlsever   select fks TABLE NAME as foreign key table name   fks CONSTRAINT NAME as foreign key constraint name   kcu foreign COLUMN NAME as foreign key column name   rc UNIQUE CONSTRAINT NAME as primary key constraint name   pks TABLE NAME as primary key table name   kcu primary COLUMN NAME as primary key column name from INFORMATION SCHEMA TABLE CONSTRAINTS fks -- foreign keys inner join INFORMATION SCHEMA KEY COLUMN USAGE kcu foreign -- the columns of the above keys     on fks TABLE CATALOG   kcu foreign TABLE CATALOG     and fks TABLE SCHEMA   kcu foreign TABLE SCHEMA     and fks TABLE NAME   kcu foreign TABLE NAME     and fks CONSTRAINT NAME   kcu foreign CONSTRAINT NAME inner join INFORMATION SCHEMA REFERENTIAL CONSTRAINTS rc -- referenced constraints     on rc CONSTRAINT CATALOG   fks CONSTRAINT CATALOG     and rc CONSTRAINT SCHEMA   fks CONSTRAINT SCHEMA     and rc CONSTRAINT NAME   fks CONSTRAINT NAME inner join INFORMATION SCHEMA TABLE CONSTRAINTS pks -- primary keys  referenced by fks      on rc UNIQUE CONSTRAINT CATALOG   pks CONSTRAINT CATALOG     and rc UNIQUE CONSTRAINT SCHEMA   pks CONSTRAINT SCHEMA     and rc UNIQUE CONSTRAINT NAME   pks CONSTRAINT NAME inner join INFORMATION SCHEMA KEY COLUMN USAGE kcu primary     on pks TABLE CATALOG   kcu primary TABLE CATALOG     and pks TABLE SCHEMA   kcu primary TABLE SCHEMA     and pks TABLE NAME   kcu primary TABLE NAME     and pks CONSTRAINT NAME   kcu primary CONSTRAINT NAME     and kcu foreign ORDINAL POSITION   kcu primary ORDINAL POSITION -- this joins the columns where fks TABLE SCHEMA    dbo  -- replace with schema name and fks TABLE NAME    your table name  -- replace with table name and fks CONSTRAINT TYPE    FOREIGN KEY  and pks CONSTRAINT TYPE    PRIMARY KEY  order by fks constraint name  kcu foreign ORDINAL POSITION   Note  There are some differences between potgresql and sqlserver implementations of information schema which make the top answer give different results on the two systems - one shows column names for the foreign key table the other for the primary key table  For this reason I decided to use KEY COLUMN USAGE view instead

User · Answer

I upgraded answer of  ollyc which is currently at top  I agree with  fionbio because key column usage and constraint column usage has no relative information at column level  If constraint column usage has ordinal positon column like key column usage  it can be joined with this column  So I made a ordinal position to constraint column usage as below  I cannot confirm this manually created ordinal position is exactly in same order with key column usage  But I checked it is exactly same order at least in my case  SELECT     tc table schema       tc constraint name       tc table name       kcu column name       ccu table schema AS foreign table schema      ccu table name AS foreign table name      ccu column name AS foreign column name FROM      information schema table constraints AS tc      JOIN information schema key column usage AS kcu       ON tc constraint name   kcu constraint name       AND tc table schema   kcu table schema     JOIN  select row number   over  partition by table schema  table name  constraint name order by row num  ordinal position                   table schema  table name  column name  constraint name           from    select row number   over  order by 1  row num  table schema  table name  column name  constraint name                   from   information schema constraint column usage                    t            AS ccu       ON ccu constraint name   tc constraint name       AND ccu table schema   tc table schema       AND ccu ordinal position   kcu ordinal position WHERE tc constraint type    FOREIGN KEY  AND tc table name    mytable

User · Answer

I created little tool to query and then compare database schema  Dump PostgreSQL db schema to text  There is info about FK  but ollyc response gives more details

User · Answer

This query works correct with composite keys also   select c constraint name       x table schema as schema name       x table name       x column name       y table schema as foreign schema name       y table name as foreign table name       y column name as foreign column name from information schema referential constraints c join information schema key column usage x     on x constraint name   c constraint name join information schema key column usage y     on y ordinal position   x position in unique constraint     and y constraint name   c unique constraint name order by c constraint name  x ordinal position

User · Answer

Issue  d  tablename on PostgreSQL prompt  in addition to showing table column s data types it ll show the indexes and foreign keys

User · Answer

I wrote a solution that like and use frequently  The code is at http   code google com p pgutils   See the pgutils foreign keys view   Unfortunately  the output is too wordy to include here  However  you can try it on a public version of the database here  like this     psql -h unison-db org -U PUBLIC -d unison -c  select   from pgutils foreign keys    This works with 8 3 at least  I anticipate updating it  if needed  in the next few months   -Reece

User · Answer

To expand upon Martin s excellent answer here is a query that lets you filter based on the parent table and shows you the name of the child table with each parent table so you can see all of the dependent tables columns based upon the foreign key constraints in the parent table   select      con constraint name      att2 attname as  child column        cl relname as  parent table        att attname as  parent column       con child table      con child schema from     select          unnest con1 conkey  as  parent            unnest con1 confkey  as  child            con1 conname as constraint name          con1 confrelid           con1 conrelid          cl relname as child table          ns nspname as child schema     from          pg class cl         join pg namespace ns on cl relnamespace   ns oid         join pg constraint con1 on con1 conrelid   cl oid     where  con1 contype    f       con    join pg attribute att on        att attrelid   con confrelid and att attnum   con child    join pg class cl on        cl oid   con confrelid    join pg attribute att2 on        att2 attrelid   con conrelid and att2 attnum   con parent    where cl relname like   parent table

User · Answer

Note  Do not forget column s order while reading constraint columns   SELECT conname  attname   FROM pg catalog pg constraint c    JOIN pg catalog pg attribute a ON a attrelid   c conrelid AND a attnum   ANY  c conkey   WHERE attrelid    schema table name   regclass  ORDER BY conname  array position c conkey  a attnum

User · Answer

Use the name of the Primary Key to which the Keys are referencing and query the information schema   select table name  column name from information schema key column usage where constraint name IN  select constraint name   from information schema referential constraints    where unique constraint name    TABLE NAME pkey     Here  TABLE NAME pkey  is the name of the Primary Key referenced by the Foreign Keys

User · Answer

One another way   WITH foreign keys AS       SELECT       conname        conrelid        confrelid        unnest conkey   AS conkey        unnest confkey  AS confkey     FROM pg constraint     WHERE contype    f  -- AND confrelid  regclass    your table   regclass   -- if confrelid  conname pair shows up more than once then it is multicolumn foreign key SELECT fk conname as constraint name         fk confrelid  regclass as referenced table  af attname as pkcol         fk conrelid  regclass as referencing table  a attname as fkcol FROM foreign keys fk JOIN pg attribute af ON af attnum   fk confkey AND af attrelid   fk confrelid JOIN pg attribute a ON a attnum   conkey AND a attrelid   fk conrelid ORDER BY fk confrelid  fk conname

User · Answer

Where  1   my schema   is the schema and  2   my table   is the name of the table  SELECT ss conname constraint name  a attname column name  ss refnamespace fk table schema  ss reflname fk table name  af attname fk column name     FROM  pg attribute a  pg attribute af           SELECT r oid roid  c conname  rf relname reflname  information schema  pg expandarray c conkey  x                  nrf nspname refnamespace  rf oid rfoid  information schema  pg expandarray cf confkey  xf             FROM pg namespace nr  pg class r  pg constraint c                  pg namespace nrf  pg class rf  pg constraint cf             WHERE nr oid   r relnamespace                 AND r oid   c conrelid                 AND rf oid   cf confrelid                 AND c conname   cf conname                 AND nrf oid   rf relnamespace                 AND nr nspname    1                 AND r relname    2  ss     WHERE ss roid   a attrelid AND a attnum    ss x  x AND NOT a attisdropped         AND ss rfoid   af attrelid AND af attnum    ss xf  x AND NOT af attisdropped     ORDER BY ss conname  a attname

User · Answer

Here is a solution by Andreas Joseph Krogh from the PostgreSQL mailing list  http   www postgresql org message-id 200811072134 44750 andreak officenet no  SELECT source table  regclass  source attr attname AS source column      target table  regclass  target attr attname AS target column FROM pg attribute target attr  pg attribute source attr     SELECT source table  target table  source constraints i  source constraints  target constraints i  AS target constraints    FROM       SELECT conrelid as source table  confrelid AS target table  conkey AS source constraints  confkey AS target constraints         generate series 1  array upper conkey  1   AS i       FROM pg constraint       WHERE contype    f         query1     query2 WHERE target attr attnum   target constraints AND target attr attrelid   target table AND       source attr attnum   source constraints AND source attr attrelid   source table    This solution handles foreign keys that reference multiple columns  and avoids duplicates  which some of the other answers fail to do   The only thing I changed were the variable names   Here is an example that returns all employee columns that reference the permission table   SELECT source column FROM foreign keys WHERE source table    employee   regclass AND target table    permission   regclass

User · Answer

the fastest to verify straight in bash answer based entirely on this answer  IFS    read -r -d    sql code  lt  lt  EOF SQL CODE       SELECT       o oid         o conname AS constraint name          SELECT nspname FROM pg namespace WHERE oid m relnamespace  AS source schema         m relname AS source table          SELECT a attname FROM pg attribute a       WHERE a attrelid   m oid AND a attnum   o conkey 1  AND a attisdropped   false  AS source column          SELECT nspname FROM pg namespace       WHERE oid f relnamespace  AS target schema         f relname AS target table          SELECT a attname FROM pg attribute a       WHERE a attrelid   f oid AND a attnum   o confkey 1  AND a attisdropped   false  AS target column         ROW NUMBER    OVER  ORDER BY o oid  as rowid       FROM pg constraint o       LEFT JOIN pg class f ON f oid   o confrelid       LEFT JOIN pg class m ON m oid   o conrelid       WHERE 1 1       AND o contype    f        AND o conrelid IN  SELECT oid FROM pg class c WHERE c relkind    r   EOF SQL CODE  psql -d my db -c   sql code

User · Answer

I think what you were looking for and very close to what  ollyc wrote is this   SELECT tc constraint name  tc table name  kcu column name   ccu table name AS foreign table name  ccu column name AS foreign column name  FROM  information schema table constraints AS tc  JOIN information schema key column usage AS kcu   ON tc constraint name   kcu constraint name JOIN information schema constraint column usage AS ccu   ON ccu constraint name   tc constraint name WHERE constraint type    FOREIGN KEY  AND ccu table name  YourTableNameHere     This will list all the tables that use your specified table as a foreign key

User · Answer

You can do this via the information schema tables  For example   SELECT     tc table schema       tc constraint name       tc table name       kcu column name       ccu table schema AS foreign table schema      ccu table name AS foreign table name      ccu column name AS foreign column name  FROM      information schema table constraints AS tc      JOIN information schema key column usage AS kcu       ON tc constraint name   kcu constraint name       AND tc table schema   kcu table schema     JOIN information schema constraint column usage AS ccu       ON ccu constraint name   tc constraint name       AND ccu table schema   tc table schema WHERE tc constraint type    FOREIGN KEY  AND tc table name  mytable

[sql] Postgres: SQL to list table foreign keys

Examples related to sql

Examples related to postgresql