sql - 如何确保依赖于值的引用完整性

Question

假设我有一张表 Level1:

Id int PK
Deleted bit

和另一个表Level2:

Id int
Deleted bit
Level1id int

我可以将 FK 添加到 Level2 以匹配 Level1id 与 Level1.Id 这在确保父项存在。

但是，当 Level1 中的匹配记录具有 时，如果我想确保记录只能在 Level2 中被 INSERTed 怎么办？已删除 bit = 0 ?

此外，当 Level2 中的任何子记录具有其 时，如何防止 Level1 中的 Deleted 标志设置为 1 >已删除 bit = 0 ?即所有子记录必须有 Deleted = 1，然后父记录才能有 Deleted = 1

我猜我可能不得不写一个触发器，但我希望有一个更优雅的解决方案...

Answer 1

正如其他人所提到的，您可以在应用程序或存储过程中实现此逻辑，但要在数据库中执行此操作，以下函数和 CHECK 约束应该可以满足您的需求寻找。

这些只会在您执行 INSERT/UPDATE 时运行/检查您感兴趣的行。

CREATE FUNCTION Level1Deleted (@Id int, @Deleted bit)
RETURNS bit
AS

BEGIN

    IF @Deleted = 1

        RETURN 0

    ELSE

        RETURN
        (
            SELECT L1.Deleted
            FROM
                Level2 L2
                JOIN Level1 L1 ON L2.Level1Id = L1.Id
            WHERE L2.Id = @Id
        )

    ;

END
GO

ALTER TABLE Level2
WITH CHECK
ADD CONSTRAINT CHK_Level1Deleted
CHECK (Level1Deleted(Id, Deleted) = 0)
GO

CREATE FUNCTION Level2NotFullyDeleted (@Id int, @Deleted bit)
RETURNS bit
AS

BEGIN

    IF @Deleted = 0

        RETURN 0

    ELSE

        RETURN
        (
            SELECT CAST(COUNT(*) AS bit)
            FROM
                Level1 L1
                JOIN Level2 L2 ON L1.Id = L2.Level1id
            WHERE
                L1.Id = @Id
                AND L2.Deleted = 0
        )

    ;

END
GO

ALTER TABLE Level1
WITH CHECK
ADD CONSTRAINT CHK_Level2FullyDeleted
CHECK (Level2NotFullyDeleted(Id, Deleted) = 0)
GO