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

Question

在我的数据库中，我创建了下表：

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

换句话说，在这种情况下，Ids 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])
)

这有外键约束，但它允许Category_Dim中的任何Id值作为我的__DogBreedId，所以一个人可以根据我的意愿，在这种情况下，除了3-5之外，还可以输入数字。

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

谢谢！！

Answer 1

为了实现这一点，我创建了一个函数，该函数基于作为父类别Id:的子级提供的CategoryId返回BIT

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 Id1="Dog Breeds"(。

所以，它确实起了作用，但它确实让我怀疑这是否是个坏主意。

Answer 2

对于单层层次结构(节点深度=1(，使用超类型子类型可能会更好。

如果确实需要一个节点深度可变的树，那么可以考虑使用闭包表，而不是在同一个表中使用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')