When you want to get all foreign key references of a table’s id. There are 2 options you can employ when you are using postgres.
1. This script one will only work if you have been assigning unique names to foreign key in your database:
SELECT tc.table_schema, 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 tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = ‘FOREIGN KEY’ AND ccu.table_name= the_name_of_the_table
2. This second script will work for all cases. you just have to supply the table in context.
select (select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns,
f.relname as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname,
(select nspname from pg_namespace where oid=m.relnamespace) as target_ns,
m.relname as target_table,
(select a.attname from pg_attribute a where a.attrelid = m.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname, o.conname as target_constraint_name from pg_constraint o left join pg_class c on c.oid = o.conrelid
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’) and f.relname = the_name_of_the_table
I do not have the luxury of time to explain in details but lemme know if you have any questions or addition. Will be happy to discuss this.
really cool!
ReplyDeleteThanks! Please correct the Code-style.. Quotes are broken when I do copy&paste.
ReplyDeleteNeither of these work. I get the following error for either one:
ReplyDelete"column \"vulns\" does not exist\n Position 783". vulns is the name of my table, and it most definitely contains foreign keys. I'm using Java to execute the query, and it fails on stmt.execute(query); (where query contains the code above)
My table is simple. It contains a bunch of crap, but the foreign key I'm interested in is named host_id. This is an id into the hosts table. I know that just by looking at it, but I need a piece of code that can walk through each column and tell me if it's a relation, and what table it links to.
I'm really surprised this isn't available in ResultSetMetaData, actually. But I digress. The code above doesn't work for postgresql :(
Charles.
Charles, which postgres version are you using? This has worked for me since postgresql-8.3 or so. I'm glad it did work for Daniel and others.
DeleteFirst option worked for me on Postgres 9.1. Just had to fix some quote chars.
ReplyDeleteI'm glad it did work for Daniel and others.
DeleteDid author ever heard that there are schemas in PG?
ReplyDeleteSchemas are namespaces used to contain objects like tables, functions, domains etc. Are you asking something specific to this post?
ReplyDeleteGreat, I've been looking into this, started out with the same one as your first one, but it didn't work, since I had multiple constraints with the same name, so your bottom one was a hit!
ReplyDeleteI needed it the other way around though - any keys in a given table, that pointed outwards, but that was just a matter of using m.relname in stead of f.relname as selector :)
select (
select nspname
from pg_namespace
where oid=f.relnamespace
) as foreign_ns,
f.relname as foreign_table,
(
select a.attname
from pg_attribute a
where a.attrelid = f.oid
and a.attnum = o.confkey[1]
and a.attisdropped = false
) as foreign_colname,
(
select nspname
from pg_namespace
where oid=m.relnamespace
) as target_ns,
m.relname as target_table,
(
select a.attname
from pg_attribute a
where a.attrelid = m.oid
and a.attnum = o.conkey[1]
and a.attisdropped = false
) as target_colname,
o.conname as target_constraint_name
from pg_constraint o
left join pg_class c on c.oid = o.conrelid
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'
)
and m.relname = 'mytable'
Interesting Fez, how you simply changed the selector to reverse the logic. I think it's great
DeleteThis comment has been removed by the author.
ReplyDeleteNeither solution works.
ReplyDeleteI have a foreign key with 2 columns. First solution gives me double records. Second solution lists only 1 column where as it should be 2.
Is there any solution?
this has saved my butt
ReplyDeleteGreat and I have a nifty present: How Much Is Home Renovation split level home remodel
ReplyDelete