[bug] Postgres should show Foreign Keys

[expired user #8358]'s profile image [expired user #8358] posted 9 years ago in General Permalink
Currently, the tab for a Table > Foriegn Keys is grayed out for Postgres tables, even if they do have foreign keys.

The following query could be used to display the information necessary to populate that tab:

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE lower(tc.constraint_type) in ('foreign key')
AND tc.table_name = ?;


Unrelated aside: I just found the feature "Tree style options" that allows me to "group objects by type" - awesome! I can't believe I missed that before; I've been wanting something like that for a while, since the Postgres objects get to be a very long list when all the functions are shown!

Please login to leave a reply, or register at first.