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

为了实现这一点,我创建了一个函数,该函数返回 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=“犬种”)。

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

对于单层层次结构(节点深度=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')