Re: Mandatory Relationship

John <jbking_at_erols.com> wrote in article <62n854$qjn$1_at_winter.news.erols.com>...
> Can anyone tell me how to code the enforcement of a mandatory
 relationship
> between a master and detail table?
> For example, I have a master table which stores primary information and
 then
[Quoted] > a detail table which stores a history of effective and expiration dates.
 A
> record in the master table must always have one or more records in the
> detail table.
> Which comes first, the chicken or the egg?
An interesting problem demands an interesting solution. You will have to perform the master row insert, along with its mandatory detail row(s) insert, as a single transaction. Use one or more PL/SQL stored procedures to do this. If no detail rows exist, either rollback the Master insert or don't insert it in the first place, depending on how you organize the code. The COMMIT can then be done after all the detail rows are successfully inserted.
In the detail table definition, the foreign key column holding the master table's primary key should be NOT NULL to enforce the requirement that it contain a value. Also you'll want to cascade deletes of the master table to remove the corresponding detail rows. And if you want to cascade the delete of the master row if all detail rows are deleted (when 1 or more existed), you'll have to write a PL/SQL stored procedure or trigger to implement this logic.
- Dan Clamage Received on Fri Oct 24 1997 - 00:00:00 CEST