By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,035 Members | 1,543 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,035 IT Pros & Developers. It's quick & easy.

Mandatory on one side of one to many?

P: n/a
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?

Any ideas?

Cheers, Mike MacSween
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Is this a SQL Server or Access or database theory question?

ANSI SQL92 allows DEFERRABLE constraints, which make it possible to
implement these circular references. Unfortunately, SQL Server doesn't
support this type of constraint. You could add an INSERT trigger to the
Production table to insert an Event automatically. Or just insert to both
tables in a transaction each time.

--
David Portas
SQL Server MVP
--
Nov 13 '05 #2

P: n/a
"David Portas" <RE****************************@acm.org> wrote in message
news:EJ********************@giganews.com...

Thanks David
Is this a SQL Server or Access or database theory question?

ANSI SQL92 allows DEFERRABLE constraints, which make it possible to
implement these circular references. Unfortunately, SQL Server doesn't
support this type of constraint. You could add an INSERT trigger to the
Production table to insert an Event automatically. Or just insert to both
tables in a transaction each time.
SQL Server 2000

Even if the inserts are wrapped in a transaction presumably the constraint
would still apply?

All I could think of was a transaction which lifted the constraint, did the
inserts, then re-applied the constraint. But seems a bit bodgy.

What database engine(s) do implement DEFERRABLE constraints?

Cheers, Mike MacSween

--
David Portas
SQL Server MVP
--

Nov 13 '05 #3

P: n/a
> Even if the inserts are wrapped in a transaction presumably the constraint
would still apply?
Correct. The constraint is applied at statement level.
All I could think of was a transaction which lifted the constraint, did
the
inserts, then re-applied the constraint.
You can use the ALTER TABLE... CHECK / NOCHECK clause to do that but there
isn't much point. SQL Server's NOCHECK doesn't defer the constraint, it
disables it. The values aren't checked and the constraint may be violated.
The ALTER statement is scoped to the database so this opens the table to
constraint violations from other connections for the duration. Better do
without the constraint and put all inserts through an SP that updates both
tables appropriately.
What database engine(s) do implement DEFERRABLE constraints?


Postgres and Oracle do. I expect there are others.

A possibile compromise is to use a nullable FK on Production. This obviously
prevents FK violations while allowing nulls.

CREATE SCHEMA AUTHORIZATION dbo

CREATE TABLE Production (prod_code VARCHAR(10) PRIMARY KEY, event_code
INTEGER NULL, FOREIGN KEY (prod_code,event_code) REFERENCES Events
(prod_code,event_code))

CREATE TABLE Events (prod_code VARCHAR(10) NOT NULL REFERENCES Production
(prod_code), event_code INTEGER NOT NULL, PRIMARY KEY
(prod_code,event_code))

--
David Portas
SQL Server MVP
--
Nov 13 '05 #4

P: n/a
"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
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.