Thread: Bug in information_schema: FK constraint is defined as against referenced table only
Bug in information_schema: FK constraint is defined as against referenced table only
From
Josh Berkus
Date:
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
Re: Bug in information_schema: FK constraint is defined as against referenced table only
From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes: > 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. Look into key_column_usage. > 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. You might think that, but the specification is 100% clear that you're wrong. regards, tom lane