代码之家  ›  专栏  ›  技术社区  ›  John Bustos

SQL Server-基于其他表中特定值的外键约束

  •  -1
  • John Bustos  · 技术社区  · 4 年前

    在我的数据库中,我创建了下表:

    CREATE TABLE [Category_Dim]
    (
        [Id]                  INT NOT NULL PRIMARY KEY
        ,[__ParentCategoryId] INT 
        ,[Name]               VARCHAR(250)
    
        ,CONSTRAINT [FK1] FOREIGN KEY ([__ParentCategoryId]) REFERENCES [dbo].[Category_Dim] ([Id])
    )
    

    这使我能够存储多种不同类型的分类(嵌套)列表,其根具有 __ParentCategoryId = NULL 然后,让孩子们按如下方式进入,例如:

    INSERT INTO Category_Dim (Id, __ParentCategoryId, Name) VALUES
        (1, NULL, 'Dog Breeds'),
        (2, NULL, 'Bird Types'),
    
        (3, 1, 'Chihuahua'),
        (4, 1, 'Pug'),
        (5, 1, 'Pit Bull'),
    
        (6, 2, 'Macaw'),
        (7, 2, 'Finch'),
        ... etc
    

    换句话说,在这种情况下,Id 3、4和;5只是1只(不同品种的狗)和6只的孩子;7是2(鸟类的类型)的孩子。


    现在,假设我正试图在我想要的位置创建第二个表 只允许狗的品种(儿童 Id = 1 )作为价值观 在列中显示错误,否则显示错误。

    到目前为止,我有以下定义:

    CREATE TABLE [Trainers]
    (
        [TrainerId] INT NOT NULL PRIMARY KEY IDENTITY (1, 1)
        ,[__DogBreedId] INT NOT NULL
        
        , ...
    
        ,CONSTRAINT [FK_DogBreeds] FOREIGN KEY ([__DogBreedId]) REFERENCES [dbo].[Category_Dim] ([Id])
    )
    

    这有外键约束,但它允许任何 Id 价值来自 Category_Dim 作为我的 __DogBreedId ,所以一个人可以按照我的意愿输入3-5以外的数字。

    有没有办法通过外键声明来实现这一点?如果没有,最好的方法是什么,或者这总体上是个坏主意吗?

    谢谢!!

    0 回复  |  直到 4 年前
        1
  •  0
  •   John Bustos    4 年前

    为了实现这一点,我创建了一个函数,该函数返回 BIT 基于 CategoryId 前提是父母CategoryId的孩子:

    CREATE FUNCTION [dbo].[IsChildOfCategory]
    (
        @__CategoryId        INT
        ,@__ParentCategoryId INT
    )
    RETURNS BIT
    AS
        BEGIN
        RETURN CASE
            WHEN EXISTS 
                (
                    SELECT Id FROM Category_Dim 
                    WHERE __ParentCategoryId = @__ParentCategoryId 
                    AND Id = @__CategoryId
                )
                THEN 1
            ELSE 0
        END
        END;
    GO
    

    然后,我在表定义中添加了以下检查约束:

    ,CONSTRAINT [CHK_IsDogBreed] CHECK ([dbo].[IsChildOfCategory]([__DogBreedId], 1) = 1)
    

    这一点,再加上外键约束,似乎完全符合我的要求。

    但是 我真的很想知道这是否是一个糟糕的模式(所有内容都作为数据存储在单个类别表中,而不是为每种类型的数据单独存储DB表),因为这让我不得不硬编码 Category Ids 就像在这个检查约束中一样,它作为DB中的数据而不是特定的DB对象存在(换句话说,它让我需要用非常具体的值为DB种子——在这种情况下,确保 Category Id 1=“犬种”)。

    所以,它确实奏效了,但它确实让我怀疑这是否是一个坏主意。

        2
  •  0
  •   Damir Sudarevic    4 年前

    对于单层层次结构(节点深度=1),使用 supertype-subtype .

    如果你确实需要一个可变节点深度的树,那么可以考虑使用 闭合表 ,而不是拥有 parent_id 在同一张桌子上。
    闭包表将所有路径存储在树中,因此每个祖先-后代链接都是一个单独的行。这样,给定节点的所有祖先/后代都会被暴露出来。闭包表易于查询,但维护起来有点困难,所以这是一种权衡。

    -- Category CAT exists.
    --
    category {CAT}
          PK {CAT}
    
    
    -- Data Sample
      (CAT)
    ------------------------
      ('Dogs')
    , ('Big Dogs')
    , ('Small Dogs')  
    , ('Chihuahua')
    , ('Pug')
    , ('Pit Bull')
    , ('Birds')
    , ('Macaw')
    , ('Finch')
    
    -- Ancestor ANC has descendant DCS
    --
    category_tree {ANC, DCS}
               PK {ANC, DCS}
    
    FK1 {ANC} REFERENCES category {CAT}
    FK2 {DCS} REFERENCES category {CAT}
    
    
    -- Data Sample, includes ANC=DCS
       (ANC, DCS)
    ------------------------
      ('Dogs'       , 'Dogs')
    , ('Birds'      , 'Birds')
    , ('Dogs'       , 'Big Dogs')
    , ('Dogs'       , 'Small Dogs')
    , ('Big Dogs'   , 'Big Dogs')
    , ('Small Dogs' , 'Small Dogs')
    , ('Dogs'       , 'Chihuahua')
    , ('Small Dogs' , 'Chihuahua')
    , ('Chihuahua'  , 'Chihuahua')
    , ('Dogs'       , 'Pug')
    , ('Small Dogs' , 'Pug')
    , ('Pug'        , 'Pug')
    , ('Dogs'       , 'Pit Bull')
    , ('Big Dogs'   , 'Pit Bull')
    , ('Pit Bull'   , 'Pit Bull')
    , ('Birds'      , 'Macaw')
    , ('Macaw'      , 'Macaw')
    , ('Birds'      , 'Finch')
    , ('Finch'      , 'Finch')
    
    -- Trainer TRA trains all descendants of ancestor ANC.
    --
    trainer {TRA, ANC}
         PK {TRA, ANC}
    
    FK {ANC, ANC} REFERENCES category_tree {ANC, DCS}
    
    
    -- Data Sample
       (TRA, ANC)
    ------------------------
      ('Joe'    , 'Dogs')
    , ('Jane'   , 'Small Dogs')
    , ('Jane'   , 'Finch')
    , ('Jill'   , 'Big Dogs')
    , ('Jack'   , 'Birds')
    , ('John'   , 'Pug')
    
    -- Trainer TRA trains DCS, descendant of ANC.
    -- (Resolved to leaf nodes.)
    WITH
    q_00 AS ( -- leaves only
    select ANC, count(1) as cnt 
    from category_tree
    group by ANC
    having count(1) = 1
    )
    SELECT t.TRA, x.DCS, t.ANC
    FROM trainer       AS t
    JOIN category_tree AS x ON x.ANC = t.ANC
    JOIN q_00 as q ON q.ANC = x.DCS
    ORDER BY TRA, t.ANC;
    ;
    

    退货:

    TRA     DCS          ANC
    ----------------------------------
    Jack'  'Finch'      'Birds'
    Jack'  'Macaw'      'Birds'
    Jane'  'Finch'      'Finch'
    Jane'  'Pug'        'Small Dogs'
    Jane'  'Chihuahua'  'Small Dogs'
    Jill'  'Pit Bull'   'Big Dogs'
    Joe'   'Pit Bull'   'Dogs'
    Joe'   'Pug'        'Dogs'
    Joe'   'Chihuahua'  'Dogs'
    John'  'Pug'        'Pug'
    

    注:

    All attributes (columns) NOT NULL
    
    PK = Primary Key
    FK = Foreign Key
    


    SQL测试

    CREATE TABLE category (
      CAT VARCHAR(32) NOT NULL
     
    , CONSTRAINT pk_cat PRIMARY KEY (CAT)
    );
    
    
    CREATE TABLE category_tree (
      ANC VARCHAR(32) NOT NULL
    , DCS VARCHAR(32) NOT NULL
    
    , CONSTRAINT pk_ctre  PRIMARY KEY (ANC, DCS)
    
    , CONSTRAINT fk1_ctre FOREIGN KEY (ANC)
                  REFERENCES category (CAT)
    
    , CONSTRAINT fk2_ctre FOREIGN KEY (DCS)
                  REFERENCES category (CAT)
    );
    
    
    CREATE TABLE trainer (
      TRA VARCHAR(32) NOT NULL
    , ANC VARCHAR(32) NOT NULL
    
    , CONSTRAINT pk_tra PRIMARY KEY (TRA, ANC)
    
    , CONSTRAINT fk1_tra FOREIGN KEY (ANC, ANC)
            REFERENCES category_tree (ANC, DCS)
    );
    
    INSERT INTO category (CAT)
    VALUES
      ('Dogs')
    , ('Big Dogs')
    , ('Small Dogs')  
    , ('Chihuahua')
    , ('Pug')
    , ('Pit Bull')
    , ('Birds')
    , ('Macaw')
    , ('Finch')
    ;
    
    INSERT INTO category_tree (ANC, DCS)
    VALUES
      ('Dogs'       , 'Dogs')
    , ('Birds'      , 'Birds')
    , ('Dogs'       , 'Big Dogs')
    , ('Dogs'       , 'Small Dogs')
    , ('Big Dogs'   , 'Big Dogs')
    , ('Small Dogs' , 'Small Dogs')
    , ('Dogs'       , 'Chihuahua')
    , ('Small Dogs' , 'Chihuahua')
    , ('Chihuahua'  , 'Chihuahua')
    , ('Dogs'       , 'Pug')
    , ('Small Dogs' , 'Pug')
    , ('Pug'        , 'Pug')
    , ('Dogs'       , 'Pit Bull')
    , ('Big Dogs'   , 'Pit Bull')
    , ('Pit Bull'   , 'Pit Bull')
    , ('Birds'      , 'Macaw')
    , ('Macaw'      , 'Macaw')
    , ('Birds'      , 'Finch')
    , ('Finch'      , 'Finch')
    ;
    
    INSERT INTO trainer (TRA, ANC)
    VALUES
      ('Joe'    , 'Dogs')
    , ('Jane'   , 'Small Dogs')
    , ('Jane'   , 'Finch')
    , ('Jill'   , 'Big Dogs')
    , ('Jack'   , 'Birds')
    , ('John'   , 'Pug')
    ;
    

    编辑

    如果整个表应仅限于一个祖先,那么您可以:

    -- Trainer TRA trains dog DCS; (ANC = 'Dogs').
    --
    dog_trainer {TRA, DSC, ANC}
             PK {TRA, DSC}
    
    FK {ANC, DSC} REFERENCES category_tree {ANC, DCS}
    
    CHECK (ANC = 'Dogs')