Dear Tom,
The REFERENTIAL_CONSTRAINTS table in the information_schema references a
constaint through its database/schema/name, but this information is not
unique, so it may identify several constraints, thus the information
derived may not be consistent.
Postgres does not enforce that constraints have unique names within a
schema. The SQL spec does say that they should be unique per-schema,
and the information_schema views are designed on that assumption.
Hence a contradiction.
If you use spec-compliant names for your constraints, you won't have a
problem. If you don't, well, the information_schema views will be of
limited use to you.
I'm writing a schema analyzer which gives false results. I do not write
the constraints, I'm analyzing existing schemas. I cannot change it.
Suggestion: constraint names could be systematically prefixed with their
corresponding table so that they are indeed unique,
We are not going to try to enforce uniqueness.
I'm not asking for uniqueness in "pg_catalog", esp as that would break
existing applications.
I'm suggesting uniqueness in the "information_schema", which can be
provided independently by some tweaking in the view construction, I think,
for instance by adding the oid of the constraint or maybe the table_name.
This has been debated before, and most people like the current behavior
just fine, or at least better than the alternatives.
I do not know "most people". I guess "most people" just do not use the
"information_schema", so they really do not care!
For the "few people" who do use the information_schema, I can assure you
that having a false information is a severe drawback, and it is called a
"bug".
So at least please fill in this as a "bug" somewhere, even if you do not
want to fix it.
--
Fabien.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
