---------- 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