要仅通过外键执行此操作,您需要添加is3D
列至Show
还有一些逻辑上多余的UNIQUE
限制。
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)
)
索引视图也可以用于以声明方式强制执行此操作,而不需要额外的列或唯一约束,如下所示。
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)
如果满足规则,基础查询应始终不返回任何行。如果有任何一行is返回然后交叉连接dbo.TwoRows
会将其相乘,导致违反唯一约束并防止出现这种情况。