sql - 引用外键并为父表中的列添加检查约束

Question

如何添加到表中 BusinessCategories检查列 IsBusinessCategory相关 Categories.ID 的值为“真” ?

CREATE TABLE [dbo].[Categories](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [DisplayNameHe] [nvarchar](400) NOT NULL,
    [DisplayNameEn] [nvarchar](400) NOT NULL,
    [DisplayNameEs] [nvarchar](400) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [Status] [bit] NOT NULL,
    [IsBusinessCategory] [bit] NULL
)



CREATE TABLE [dbo].[BusinessCategories](
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [BusinessCategoryID] [INT] NOT NULL REFERENCES Categories(ID) -- Need to check that is 
     businessCategory ==1
    [BusinessID] [INT] NOT NULL REFERENCES Business(ID)
)

目标是能够插入到列 业务类别ID 仅来自 的值分类 具有 的表IsBusinessCategory == true .

Answer 1

我不推荐那种解决方案，应该在应用程序的业务层完成

在 SQL 方面有一个棘手的方法，不是最佳的，但可行的......

首先创建一个标量函数，返回所选类别的 isBusinessCategory 值

CREATE FUNCTION fn_isBusinessCategory (@CatID INT) RETURNS INT
AS
BEGIN
    RETURN (SELECT isBusinessCategory FROM Categories WHERE CategoryID = @CatID)
END
GO

创建另一个标量函数，如果在 BusinessCategories 中引用给定类别，则该函数返回 1

CREATE FUNCTION fn_isBusinessCategoryValid (@CatID INT, @isBusinessCat BIT) RETURNS BIT
AS
BEGIN
    IF @isBusinessCat = 1
        RETURN 1
    ELSE IF EXISTS (SELECT 1 FROM BusinessCategories WHERE CategoryID = @CatID)
        RETURN 0
    ELSE
        RETURN 1
END
GO

添加以下约束

步骤 1 验证两个表之间完整性的外键

ALTER TABLE BusinessCategories ADD CONSTRAINT FK_BusinessCategory 
FOREIGN KEY (CategoryID) 
REFERENCES Categories (CategoryID)

步骤 2 检查约束以验证该类别 is_businessCategory

ALTER TABLE BusinessCategories ADD CONSTRAINT ck_BusinessCategory 
CHECK (dbo.fn_isBusinessCategory(CategoryID) = 1)

步骤 3 检查约束以防止使用时将类别更改为非业务类别

ALTER TABLE Categories ADD CONSTRAINT ck_Category_isBusinessCategory 
CHECK (dbo.fn_isBusinessCategoryValid(CategoryID, isBusinessCategory) = 1)