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