Values controlled by foreign keys

Question

I have very simple database in SQL Server, with these three tables:

• Theater (ID, is3D, othervalues...)
• Show (ID, Theater_ID, Movie_ID, date, time, othervalues...)
• Movie (ID, is3D, othervalues...)

I would like to ensure that 3D movies can be only played in 3D theaters. 2D movies only in 2D theaters, and doing it ONLY BY foreign keys (no triggers etc.).

Answer 1

To do this through foreign keys alone you need to add an is3D column to Show as well and a couple of logically redundant UNIQUE constraints.

CREATE TABLE Theater
  (
     ID   INT PRIMARY KEY,
     is3D BIT NOT NULL,
     /*Other columns*/
     UNIQUE(ID, is3D)
  )

CREATE TABLE Movie
  (
     ID   INT PRIMARY KEY,
     is3D BIT NOT NULL,
     /*Other columns*/
     UNIQUE(ID, is3D)
  )

CREATE TABLE Show
  (
     ID         INT PRIMARY KEY,
     Theater_ID INT NOT NULL,
     Movie_ID   INT NOT NULL,
     is3D       BIT NOT NULL,
     /*Other columns*/
     FOREIGN KEY(Theater_ID, is3D) REFERENCES Theater (ID, is3D),
     FOREIGN KEY(Movie_ID, is3D) REFERENCES Movie (ID, is3D)
  ) 

An indexed view can also be used to enforce this declaratively without requiring the additional column or unique constraints as below.

CREATE TABLE dbo.TwoRows
  (
     X INT PRIMARY KEY
  );

INSERT INTO dbo.TwoRows
VALUES      (1), (2)

GO

CREATE VIEW V
WITH SCHEMABINDING
AS
  SELECT S.Theater_ID,
         S.Movie_ID
  FROM   dbo.Show S
         JOIN dbo.Theater T
           ON S.Theater_ID = T.ID
         JOIN dbo.Movie M
           ON S.Movie_ID = M.ID
         CROSS JOIN dbo.TwoRows
  WHERE  T.is3D <> M.is3D

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON V(Theater_ID, Movie_ID) 

The underlying query should always return no rows if the rule is satisfied. If any row is returned then the cross join on dbo.TwoRows will multiply it out causing a unique constraint violation and preventing the situation.