Re: Mandatory on one side of one to many?

"Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message news:<413ae0f6$0$6335$5a6aecb4_at_news.aaisp.net.uk>...
> Production (one) to events (many)
>
> Events must have a production (easy, non null FK)
>
> A Production doesn't make (business) sense without an event. How to enforce
> a mandatory relationship on the one side?

Logically, the constraint you want goes something like this:

create constraint ProductionValid
  not exists ((Production over { ID })
    minus (Events over { Production_ID } rename { Production_ID ID }))

It's a not quite a foreign key, because it targets a non-key column of the Events table, but the idea is the same. (Inclusion dependency is the formal term, of which foreign keys are a special case). From an implementation standpoint, I know of only one system that would actually allow you to enforce such a constraint. I could be wrong because a system that allowed deferred event handlers would allow this as well, but I know of only one that allows this as well. The system is Dataphor, and you could use the following transition constraint definitions to enforce it in this product:

create table Production { ID : Integer, ..., key { ID } };

create table Event { ID : Integer, Production_ID : Integer, ..., key { ID } };

create reference Event_Production
  Event { Production_ID } references Production { ID };

alter table Production
{
 create transition constraint ProductionValid   on insert exists (Event where Production_ID = new.ID)   on update (old.ID = new.ID) or exists (Event where Production_ID = new.ID)
};

alter table Event
{
 create transition constraint ProductionValid   on update (old.Production_ID = new.Production_ID) or     exists (Event where Production_ID = old.Production_ID)   on delete
    exists (Event where Production_ID = old.Production_ID) };

The transition constraints enforce the requirement that a given production have at least one event at all times. The system infers that because the transition constraint definitions reference the database, validation must be deferred to transaction commit. Dataphor is the only system I know of that would allow such a constraint to be enforced server-side. Every other solution requires client-side enforcement, I'm afraid.

Hope this helps,
Bryn Rhodes
Alphora Received on Sun Sep 05 2004 - 22:52:20 CEST