Thank you very much Philip, your sugestions are interestings.

Greetings.

Walter.

On Fri, Aug 26, 2011 at 4:50 PM, unordained <unordained_00@...>wrote:

> **
>
>
> ---------- Original Message -----------
> From: W O <sistemas2000profesional@...>
> > Yes, because a parent without a child has not sense in my application.
> > So, I want to avoid that possibility.
> > Walter.
> >
> > On Sat, Aug 20, 2011 at 10:29 AM, Ann Harrison <aharrison@...
> >wrote:
> > > OK, let me try to restate that. The foeeign key constraint requires
> that
> > > each child have qa parent. You also want to require that each parent
> have at
> > > least one child. The obvious question is how do you ever store a new
> parent,
> > > in the absence of deferred constraints. You could use triggers.
> > > Ann
> ------- End of Original Message -------
>
> Ann is pointing you down the right track. More precisely, I'd recommend you
>
> look at on-commit (transaction) triggers, which effectively let you write
> most
> deferred constraints. (Not appropriate for deferred unique/foreign-key
> constraints, but you don't need those today.)
>
> I'd recommend:
>
> * create a global temporary table (transaction-scoped) to store a list of
> parents you've touched during the current transaction.
> * create triggers on the parent and child tables, so that on insert/update/
> delete (especially insert of parent, and delete of child, and update to
> parent_id on child, in which case both the old and new parent should be
> marked
> for re-checking) you update-or-insert the parent_id into the temporary
> table.
> * create an on-commit trigger, which will look at every parent_id in the
> temporary table, for which a parent still exists in the main table (you
> don't
> care about problems with recently-deleted parent records), and for each
> one,
> make sure it has at least one child. If you find a parent record that has
> been
> touched during the transaction that lacks any children, throw an exception
> to
> refuse to commit a change that results in invalid data (parent with no
> children.)
> * optionally, move the code for the trigger into a procedure (which you
> call
> from the trigger), and have the procedure clear the temporary table if it
> runs
> successfully; this allows you to run the procedure, at random, in the
> middle of
> a transaction, to verify that everything is "still okay", without actually
> committing, and without doing double-work (check now and check again at
> commit,
> for no reason.)
>
> -Philip
>


[Non-text portions of this message have been removed]