Re: Mandatory on one side of one to many?

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