Re: Is this bad design ?

>
> In a DBMS (e.g. Oracle) that doesn't support check/assertion
> constraints that reference more than one table, you can't do what you
> would really want to do, which would be something like:
>
> ALTER TABLE mother ADD CONSTRAINT x
> CHECK (still_valid OR NOT EXISTS (SELECT * FROM child c WHERE
> c.mother_id = mother.mother_id));
>
> (Not necessarily correct SQL syntax).
>
> Your inclusion of the still_valid column in the child table is a
> work-around for that limitation.

Yes, and most people consider this 'ugly'.

>
> An alternative would be to use database triggers, but you would need
> triggers on both tables and all events that might lead to a violation.

That would not work because during the checking of the trigger there is a possibility that the still_valid column alters to false. (When inserting a first child).
(Trans 1 inserts a child a trigger checks if the parent is valid, because of read-consistency it is.
Trans 2 sets the still_valid to false, a trigger checks if there are not childern,
because of read-consistency the transaction does not see children).

(The trigger has this limitation, or a transaction doing the check has the same limitation).

ben brugman Received on Tue Mar 09 2004 - 14:36:05 CET