Postgres Foreign Keys

Richard2048
Richard2048 used Ask the Experts™
on
I wish to write a query that will return a list of foreign keys and the table to which they pertain.
For example:
- Given a table of 'customers' and a table of 'accounts', in which each customer can have 1 or more accounts
- The FK in accounts is a customer_id

How would I write a query to list the foreign key(s) in the accounts table along with the table to which said foreign key relates.
For exampe:
I want to query the 'accounts' table, so as to retrieve the customer_id as the FK and the customers table as the table linked to this FK.

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
psql --quiet --echo-hidden dbname
\d accounts
Commented:
To get a listing of the foreign key constraints of a table, and the tables to which they reference, run the following query.  (I've used your 'accounts' table here.  Replace with other tables as you'd like.)  'l_table' is the table you're interested in, and 'f_table' would be the table referenced by the foreign key.

You will see that I also include the 'conkey' and 'confkey' columns.  These are the column numbers for the 'l_table' and 'f_table'.  You can manually get the names by doing '\d <tablename>' and count down that many.  To use SQL, we would join the 'pg_attribute' table into the query, but 'conkey' and 'confkey' are arrays, and I'll admit that I'm having trouble figuring out how to unwind the array in order to perform the join.  This also depends on what version of PostgreSQL you are using.  8.4 appears to have more array functions than 8.3, and they both have more array operators than 8.1.

However, if all of your foreign keys are single-column references, the second query below will work.  (Obviously not a general solution.)
SELECT conname, ltable.relname as l_table, ftable.relname as f_table, 
  conkey, confkey 
FROM pg_constraint 
 JOIN pg_class ltable ON (pg_constraint.conrelid = ltable.oid) 
 JOIN pg_class ftable ON (pg_constraint.confrelid = ftable.oid) 
WHERE pg_constraint.contype = 'f' and l_table.relname = 'accounts'
 
 
SELECT ltable.relname as l_table, ftable.relname as f_table, conkey, attnum, attname 
FROM pg_constraint 
  JOIN pg_class ltable ON (pg_constraint.conrelid = ltable.oid) 
  JOIN pg_class ftable ON (pg_constraint.confrelid = ftable.oid)
  JOIN pg_attribute ON (pg_constraint.conrelid = pg_attribute.attrelid 
    AND pg_constraint.conkey = ARRAY[pg_attribute.attnum]) 
WHERE pg_constraint.contype = 'f' AND ltable.relname = 'accounts'

Open in new window

Author

Commented:
Perfect,
For the most part, all I have are single column references.  The second query you posted certainly brought a smile to my face!

Thankyou!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial