"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<41**********************@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
