Re: circular relationships ok?

From: Roy Hann <specially_at_processed.almost.meat>
Date: Thu, 2 Mar 2006 19:05:22 -0000
Message-ID: <>

"-CELKO-" <> wrote in message
> Technically, you can declare cycles with a CREATE SCHEMA statement
> which brings all the schema objects into being all at once.
> But the practical results are generally bad. A simple A->B and B->A
> cycle can prevent you from inserting or deleting from both tables ("To
> get a job, you need experience; to get experience, you need a job").

Whether that is wrong depends on the real business rule. If the constraint wrongly imposes a restriction that doesn't exist in the real world then it is a database design error. However if it is a real rule in the real world but the tool won't enforce it, then the tool is inviting corruption.

> The other "gotcha" is A->B, A->C and B->C with cascaded actions. I
> change A, which fires actions in both B and C. The change in B fires
> an action in C. But the changes to C are different on the same rows
> (say SET NULL and SET DEFAULT); which one takes effect? I one early
> version of DB2, the answer was whoever was the last guy to touch C --
> unpredictable.

This is an implementation detail that can be got right or got wrong, not an argument for refusing to support the rules of the business. (It is also, dare I say, a glaring example of why in the 21st century we should be asking why we are still satisfied only to be able to make one incremental update to one row of one table at time.)

Roy Received on Thu Mar 02 2006 - 20:05:22 CET

Original text of this message