Re: Distributed foreign keys (was Re: Category Types)

"Bob Badour" <bbadour_at_golden.net> wrote in message news:kFFJa.361$ez1.56242211_at_mantis.golden.net... [snip]
> This is true. However, the way one gets the data into the database is very
> different. The triple-relation approach requires one to explicitly state
> which of the three relations the referenced value belongs in when one first
> inserts the value. The view approach requires explicit insertion for two of
> the three relations and prohibits explicit insertion in the third. Or am I
> missing something?

I don't like view updating...

> > > The distributed key requires
> > > one to insert the value into two relations. Using a view would require
> one
> > > to sometimes insert a value into two relations for two of three cases
> and
> > > prevent one from inserting a value into two relations in the other case.
> >
> > It's not really a matter of prevention. If a person's salary is unknown,
> then
> > for a new person, you insert into Person only.
>
> Exactly, and if you did insert into the view, you would get a duplicate
> error either on the insert to the view or on the insert to Person. The
> insert is required for two cases and prohibited for the third. I dislike the
> asymmetry.

I don't like view updating...

.. and the fact that you can try to insert contradictory data (or 'duplicate' data - whatever that might mean in the terms of the relational model) into a database is but one reason why I don't like view updating AT ALL.

> > > I find the asymmetry of the view approach unappealing.
> >
> > I don't like asymmety, but I don't really see any here. If we take the
> closed
> > world assumption, anything that we don't know anything about does not get
> > inserted into the database. A distributed foriegn key should not be
> limited to
> > the 'complete' case.
>
> Why not? What's the point of a foreign key that says a value might or might
> not reference a tuple in some relation? Isn't the whole point of a foreign
> key to state that every value must reference a tuple in some relation?

Well current SQL foreign keys only cover the 'incomplete' (i.e. 1 to 0-1-M ) case. "every value must reference a tuple in some relation" works one way (from FK to PK) but not both ways.
Even plain FKs should support the 'complete' case. IMO this is one area that SQL is seriously lacking (but is but an example of it's poor integrity support in general). Of course the gotyah is that you either need deferred constraint checking or multiple-assignment..

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Mon Jun 23 2003 - 18:52:48 CEST