"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
interesting argument and one that come back often;

Really? I can put my hand on my heart and say I've never seen anyone
suggest it when they cross-post to comp.databases.theory. I don't doubt it
has come up, but I do doubt it is often.

however it's not a silver buller.

I implicitly allowed that it is not a silver bullet by actually suggesting
the kinds of reasons one might exclude it.

First of all, it's another level of complexity that you must add to the
design of your database; ie, you must make sure that they are all there
and no one is missing.

Of course one must make sure "they are all there"; you are absolutely right.
But it absurd to suggest doing that is "another level of complexity". It is
trivial to do it, and trivial to check that you've done it by querying the
DB catalogs.

Second, this DRI cannot be used with cyclic relationship with SQL-Server
but with Oracle, you can. (From your example, I believe that you are
working with Oracle).

I'm not. But the fact that you are distinguishing the behaviour of
particular products gets close to the real problem. The real problem is
that the products we use are all more or less defective, but instead of
clamouring to have them fixed (by establishing suitable standards and
following them) we promote workarounds as if they are actually desirable. I
have no problem at all with people describing workarounds for defects but I
have a major problem when it is implied that the workaround is some kind of
best-practice or even desirable.

On SQL-Server, you must use triggers to implement such a feature when
there is a cyclic relationship. Of course, when you are dealing with tens
and hundreds of relationships, this can quickly translate into a
nightmare. There is also the qestion of the diminution of performance and
of general design: when you have to update multiples records on multiple
tables for what should be the change of a single value in a single table
make it hard to believe that this is a proper normalized database design

First of all, updates to keys should be very rare. A fundamental property
of a well-chosen key is that it is reasonably stable. Imposing the little
self-discipline required to make sure you choose stable keys is
inconsequential when compared to the overall database design effort.

Secondly, even having to update hundreds of tables to amend a key is only
about the same effort required to insert all those rows in the first place.
Against the background of work the system does all the time, that will be
inconsequential. (Of course, if you unwisely choose a key that is not
stable, your argument would be more nearly correct. But that is why the
long-standing advice has been to avoid keys that are not stable.)

and this situation quickly worsen if you have to take into account the
correspondance with backups, reports and linked databases; all systems for
which there is no automatic DRI.

I don't entirely agree with all these reasons, but as I said in my earlier
post, there often *are* good reasons why one might not be able to use ON
UPDATE CASCADE in a particular product and I will take your word for it that
these reasons apply with the product you use. My challenge to you was to
signal you know that, and you have now done so.

But why make it simpler when you can make it harder?

Hm.

Finally, I don't understand your example at all. You are introducing us
to the NATURAL JOIN and USING statement that have been introduced by
Oracle in its 9i version (also in MySQL and Postgres, I believe) but I
fail to see what this has to do with the subject of this thread; the use
of a separate PK in a junction table and its highly related topic, ie. the
use of natural keys versus the use of surrogate keys. There is no
relationship at all between a NATURAL JOIN and a natural key and the
Natural Join can be used as easily with a surrogate key than with a
natural key.

I am confused about your argument here. I was giving counter-example to
disprove the claim that composite keys make the SQL code more complex, which
was being presented as an argument to introduce yet more, spurious,
synthetic/surrogate keys. In fact if you read my example carefully, you
will have seen that I talked explicitly about *three* synthetic keys because
I aware I was already using two (order number and item number).

The only thing that is important with the Natural Join is the name of the
key. (BTW, if you were to ask me what I'm thinking about this little
monstruosity, I would tell you that this is a perfect example of a Pandora
box.).

Well, I have to admit that I'm not over-fond of relying on names to imply
that two columns represent the same thing, so in fact I never use that
syntax. I do prefer to assert all the conditions on all the key columns
explicitly and I just don't notice the few extra keystrokes when it's a
composite key. But on the other hand, I find it monstrous when I see two or
more distinct names for colunms that do represent the same thing.

And finally, a for your request of asking me to convince you that I'm
properly understand the problem here of to etablish that I'm credible: I
can tell you that I have absolutely no intention of doing it

And yet you have greatly increased your credibility with this post. I still
disagree with what you've said, but I can see you know more about what
you're talking about than it seemed before. Before, I thought you were
ignorant and uncurious. Now I see you are merely wrong. :-)

and that I have absolutely no interest at all about what you are thinking
of me.

That's very healthy. You shouldn't.

The only things that are of interest to me are the arguments that I'm
seeing posted here - whatever the people who might write them - but for
someone who has just make a confusion between a natural key and the NATURAL
JOIN, asking for such a thing make it looks very strange.

I make no such confusion, and a quick glance at my earlier post will confirm
it.

Roy