Re: [HACKERS] 7.1beta4 bug creating a certain table

On Tue, 30 Jan 2001, Kovacs Zoltan wrote:

> > It appears to me that this is correct, since there is no constraint
> > on the first column that says that those three columns form a unique
> > key *by themselves*.  I believe there were bugs in the code that checked
> > for this error before ...
> > 
> >             regards, tom lane
> > 
> I don't know the exact SQL definition whether my declaration is correct or
> not. But, checking our model, we realized that our implementation is not
> correct. So I should rewrite these definitions. Thanks! :-)
> 
> But, if this declaration is not correct in the sense of SQL standards, I
> can imagine that PostgreSQL may allow such declarations. (A NOTICE may be
> given instead of an ERROR.) Or, are there any drawbacks of allowing this?

Yes.  The columns *must* be unique because the logic that the foreign keys
are defined to use doesn't really make sense for match unspecified and
match full if they can have duplicates.

You'd need logic like that for match partial for all of the cases for it
to really make sense.  For example, if you can have two pk rows with key
values (1,2) and a fk row (1,2) and you update one of the two pk rows to
(2,3) what happens on an on update cascade?  Match partial still
"requires" that the keys be unique but since fk rows can already match
more than one pk row it's probably not as big a deal.  Of course, match
partial is a real pain because you need to keep stuff around about what
*other* rows were modified during the statement while you're running the
trigger.