Xavier Tarifa <[email protected]> writes:
> how do you go about finding information about constraint columns? I
> was trying to use the view information_schema.constraint_column_usage
> but I've found out that to uniquely identify a constraint it would
> need to show the constraint table, but in case of foreign keys it only
> shows the table of the referenced column.
Yeah. The back story here is that the SQL standard says that
constraint names are to be unique per-schema. So they defined
information_schema.constraint_column_usage on the assumption
that constraint_schema + constraint_name is a unique identifier.
Postgres of course considers constraint names to be local to a
table, so that different tables in the same schema can use the
same constraint name. So you can either design your app to
obey the spec's restriction, or use something other than
constraint_column_usage to get info about your constraints.
Neither end of this is likely to change. We're not going to
extend constraint_column_usage with extra columns: the entire
point of information_schema is to present an exactly
standards-compliant view of the catalogs. And we're not going
to require constraint names to be unique per-schema either;
even if we wanted to do that, it'd doubtless break too many
existing applications.
regards, tom lane
