Re: enforce no master without detail

From: Phil Herring <phil_herring_at_yahoo.com.au>
Date: Wed, 5 Aug 2009 03:04:03 -0700 (PDT)
Message-ID: <908939f6-7d32-4982-bef6-189ed67eeca1_at_d4g2000prc.googlegroups.com>

It helps if you say which version of Oracle you're using.

One possible solution for 10g and 9i:

1. Numeric "child counter" column on the parent.
2. Insert and delete triggers on the child that increment and decrement (respectively) the child counter.
3. A deferred check constraint on the parent that specifies that the child counter > 0.

A possible (equivalent) solution for 11g:

1. A deterministic function that counts the rows in the child table.
2. A virtual column on the parent that calls the function to count the children.
3. A deferred check constraint on the parent that specifies the virtual column > 0.
   • Phil

Received on Wed Aug 05 2009 - 05:04:03 CDT