Re: multi column foreign key for implicitly unique columns

On Tue, 17 Aug 2004, Markus Bertheau wrote:

> , 17.08.2004, 17:06, Stephan Szabo :
> > On Tue, 17 Aug 2004, Markus Bertheau wrote:
> >
> > > , 17.08.2004, 16:46, Tom Lane :
> > >
> > > > I think one reason for this is that otherwise it's not clear which
> > > > unique constraint the FK constraint depends on. Consider
> > > >
> > > > create table a (f1 int unique, f2 int unique);
> > > >
> > > > create table b (f1 int, f2 int,
> > > > foreign key (f1,f2) references a(f1,f2));
> > > >
> > > > How would you decide which constraint to make the FK depend on?
> > >
> > > Either way, the semantics are the same, right?
> >
> > Unfortunately, not in the case of dropping the chosen constraint.
>
> Can't you choose at fk check time rather than fk creation time?
>
> > Theoretically in that case, you'd probably have to extend the spec there
> > as well to say that you check any dependent objects again to see if they
> > would still be valid rather than dropping them (on cascade) or erroring
> > (on restrict).
>
> That also makes sense and is more efficient as I see it.

I'm not seeing what you're seeing then.

Right now, at creation, we can say object A depends on object B. When you
go to drop object B, we can easily lookup up which objects (A) depend on
it. When you go to drop object C, we can easily lookup up which objects
() depend on it.

If instead you put it off to drop time, when you drop object B, you need
to figure out which objects might potentially depend on be (lets say
(A,C)) and then determine which objects those do depend on and see if B is
among those sets.

If we do the in-between one, we could say that object A partially depends
on B (because something else can fufill the requirement as well
potentially). When you go to drop object B, we can see that A partially
depends on B and then check only A's dependencies to see whether any other
thing that might fufill the requirement still exists. In general, such a
system would need to be able to make sure that it worked properly with
multiple concurrent drops of objects that an object partially dependended
on (even though the constraint case is probably safe.) It sounds like
it'd be a pain at best.