Folks,
I've been trying to extract some information about referencing tables
from information_schema, and discovering that it isn't there.
For example, take the following FK, from table Errors to table Files:
CONSTRAINT errors_file_fkey FOREIGN KEY (file) REFERENCES files (id) MATCH SIMPLE ON UPDATE CASCADE ON
DELETECASCADE
What's listed in referential_constraints is:
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema |
unique_constraint_name | match_option | update_rule | delete_rule apple3 | public |
errors_file_fkey| apple3 | public | files_pkey |
NONE | CASCADE | CASCADE
Ok, there's some useful information about the *referenced* table,
including its unique constraint. Doesn't tell us anything about the
*referencing* table, though.
Let's look up the data on errors_file_fkey constraint in
constrain_column_usage:
table_catalog | table_schema | table_name | column_name |
constraint_catalog | constraint_schema | constraint_name apple3 | public | files | id |
apple3 | public | errors_file_fkey
Huh? This shows errors_file_fkey constraint defined on the *referenced*
table only.
constraint_table_usage has this:
table_catalog | table_schema | table_name | constraint_catalog |
constraint_schema | constraint_name
---------------+--------------+------------+--------------------+-------------------+------------------ apple3 |
public | files | apple3 |
public | errors_file_fkey
By information_schema, you wouldn't have any idea that errors_file_fkey
is defined on the table Errors, let alone what columns it's defined
against.
Poke around; you'll discover that there is no information about
referencing tables in information_schema at all.
This has got to be a bug, even if the SQL standard can be read to
support it.
I'm happy to write some code to fix it, if we can agree what these views
should show. I think constraint_column_usage and constraint_table_usage
should be showing the data of both the referenced and referencing tables.
--Josh Berkus