Thank you very much Philip, your sugestions are interestings. Greetings.
Walter. On Fri, Aug 26, 2011 at 4:50 PM, unordained <[email protected]>wrote: > ** > > > ---------- Original Message ----------- > From: W O <[email protected]> > > 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 <[email protected] > >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]
