由外键控制的值 [英] Values controlled by foreign keys

问题描述

我在SQL Server中有非常简单的数据库，有这三个表：

• Theatre （ ID，is3D， othervalues ...）


• 显示（ ID，Theater_ID，Movie_ID，日期，时间， othervalues ...）
  
• code>（ ID，is3D， othervalues ...）
  
  
  

  我想确保 3D电影只能在3D影院播放。 2D电影只能在2D影院，并且只能通过外键（无触发器等）进行。

  要单独通过外键来完成这项工作，你需要在 Show 中添加一个 is3D 列以及一些逻辑冗余 UNIQUE 约束。
  
  
  $ $ $ $ $ $ $ $ $ $ b ID INT PRIMARY KEY，
  is3D BIT NOT NULL，
  / *其他列* /
  UNIQUE（ID，is3D）
  ）
  
  CREATE TABLE Movie
  （
  ID INT PRIMARY KEY，
  is3D BIT NOT NULL，
  / *其他列* /
  UNIQUE（ID，is3D）
  ）
  
  CREATE TABLE显示
  （
  ID INT PRIMARY KEY，
  Theater_ID INT NOT NULL，
  Movie_ID INT NOT NULL，
  is3D BIT NOT NULL，
  / *其他列* /
  FOREIGN KEY（Theater_ID，is3D）参考剧院（ID，is3D），
  FOREIGN KEY（Movie_ID，is3D）参考电影（ID，is3D）
  ）
  

  索引视图也可以用来强制执行声明式地不需要额外的列或独特的约束如下。
  
  

    CREATE TABLE dbo.TwoRows 
  （
   X INT PRIMARY KEY 
  ）; 
   
   INSERT INTO dbo.TwoRows 
   VALUES（1），（2）
   
   GO 
   
  创建视图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 
   
  创建唯一的群集索引IX 
   ON V（Theater_ID，Movie_ID）
    

  如果规则满足，底层查询应该总是不返回任何行。如果任何一行被返回，那么 dbo.TwoRows 上的交叉连接会将其相乘，导致唯一的约束违例并防止出现这种情况。

  
  

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

  解决方案

  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.

  这篇关于由外键控制的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！