Postgres: SQL to list table foreign keys

You can do this via the information_schema tables. For example:

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 tc.table_name='mytable';

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"
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.confrelid, 
        con1.conrelid
    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

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

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

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';

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

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.

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 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

I think what you were looking for and very close to what @ollyc wrote is this:

SELECT
tc.constraint_name, tc.table_name, tc.constraint_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

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

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.