Re: Multicolumn foreign keys need useless unique indices?

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> so the index will help us equally for both cases, as it will point to N
> entries of which only one can be alive at a time and which all have to
> be checked.
> It will be only marginally more work to check if the only live entry
> does match the non-index columns.

But the "marginally more work" represents code that does not exist at
all right now, and which there's no really convenient place to add AFAIR.
This seems to me to be going rather out of our way to support a coding
practice that is specifically disallowed by the standard.

Something that no one has bothered to ask, but seems to me relevant,
is exactly why we should consider it important to support foreign keys
of this form? Aren't we talking about a poor schema design in the first
place, if the referenced column set covers more than just the unique key
of the referenced table? At the very least this is a violation of
normalization, and so it's inherently inefficient.

> There will probably be additional work if we want to drop the original
> constraint, but this is a separate issue.

It's not separate, because it's more work that we *will* have to do,
to support a feature that is nonstandard and of debatable usefulness.

regards, tom lane