主页 / dba / 问题 / 213924
Accepted
Ross Presser
Asked: 2018-08-03 08:04:13 +0800 CST

防止删除最后一个孩子

  • 772
CREATE TABLE Parent ( ParentID int NOT NULL PRIMARY KEY,
                      ParentName varchar(50) NULL )
CREATE TABLE Child  ( ChildID int NOT NULL PRIMARY KEY,
                      ParentID int NOT NULL REFERENCES Parent (ParentID),
                      ChildName varchar(50) NULL,
                      IsFavorite BIT NOT NULL )

第一个愿望是想防止无子父母的情况发生。显然，当第一次创建该对时，父对象将没有子对象，我不想阻止插入本身；但我想彻底禁止删除最后一个孩子。（出于商业原因，Parents 永远不会被删除。）

作为第二个愿望，我需要阻止多个 IsFavorite=1 的 Child，我知道我可以使用过滤的唯一索引来做到这一点。我还想劝阻没有 IsFavorite=1 的子项——即，如果这是其父项的唯一子项，则不允许设置 IsFavorite=0。（从应用程序方面来看，对于第一个孩子，插入通常应该使用 IsFavorite=1 正确完成；但我不希望数据库干扰它。）

我确定我不是第一个有这些愿望的人，但我在 dba 或 stackoverflow 上找不到匹配的问题。如果这是一个骗局，一定要把它标记为骗局，并指出我需要去的地方。

sql-server foreign-key

2 个回答

  1. Best Answer

    您可以在 Parent 上创建 (ParentId,FavoriteChildId) FK 并获得其中的大部分。然而，在 SQL Server 中，没有实用的方法来建立所需的关系，因此您必须允许父项没有最喜欢的子项，否则您将无法在不禁用 FK 的情况下插入新的父项。

    您可以添加触发器以防止以后将 FavoriteChildId 设置为 null，但至少在 Parent 有一个 Child 的情况下必须允许它，否则将无法删除该 Child。所以我不确定是否值得为 Trigger 烦恼。例如：

    --alter table Parent 
--drop constraint fk_Parent_favorite_child
--go
--drop table if exists Child
--drop table if exists Parent


go

CREATE TABLE Parent 
( 
   ParentID int NOT NULL PRIMARY KEY,
   ParentName varchar(50) NULL,
   FavoriteChildId int null
)

CREATE TABLE Child  
( 
  ParentID int NOT NULL REFERENCES Parent (ParentID),
  ChildID int UNIQUE NOT NULL,
  constraint pk_child primary key (ParentID, ChildID),
  ChildName varchar(50) NULL
)

alter table Parent 
add constraint fk_Parent_favorite_child
foreign key (ParentId,FavoriteChildId) 
  references Child (ParentId,ChildId)
on delete set null

go
create or alter trigger tg_ensure_favorite_child 
  on parent after update 
as
begin
   if exists 
   (
       select * 
       from inserted i
       where i.FavoriteChildId is null
         and (select count(*) from child where ParentID = i.ParentId  ) > 1
    )
    begin
      throw 50001, 'Cannot set ChildID null for Parent with more than one Child.', 10
    end
end

go

insert into Parent(ParentID,ParentName) values (1,'Parent1')
insert into Child(ParentId, ChildId, ChildName) values (1,1,'Child1')
insert into Child(ParentId, ChildId, ChildName) values (1,2,'Child2')
update Parent set FavoriteChildId = 2 where ParentId = 1

go

update parent set FavoriteChildId = null where ParentId = 1 -- Fails
--Msg 50001, Level 16, State 10, Procedure tg_ensure_favorite_child, Line 13 [Batch Start Line 45]
--cannot set ChildID null for Parent with Children

select FavoriteChildId from Parent where ParentID = 1
--FavoriteChildId
-----------------
--2

--(1 row affected)
go

delete from Child where ParentId = 1 and ChildID = 1

go

update parent set FavoriteChildId = null where ParentId = 1 -- Succeeds
    • 2

  2. AFTER使用过滤的唯一索引和几个触发器可能最容易实现这些（有些不寻常的）要求：

    表和索引

    CREATE TABLE dbo.Parent
( 
    ParentID int NOT NULL,
    ParentName varchar(50) NULL,

    CONSTRAINT [PK dbo.Parent ParentID]
        PRIMARY KEY CLUSTERED (ParentID)
);

CREATE TABLE dbo.Child
( 
    ChildID int NOT NULL,
    ParentID int NOT NULL,
    ChildName varchar(50) NULL,
    IsFavourite bit NOT NULL,

    CONSTRAINT [PK dbo.Child ChildID]
        PRIMARY KEY CLUSTERED (ChildID),

    CONSTRAINT [FK dbo.Child dbo.Parent (ParentID)]
        FOREIGN KEY (ParentID)
        REFERENCES dbo.Parent (ParentID)
);
GO
-- No more than one favourite child per parent
CREATE UNIQUE INDEX [FUQ dbo.Child ParentID (IsFavourite = true)]
ON dbo.Child (ParentID)
WHERE IsFavourite = CONVERT(bit, 'true');

-- Useful index for trigger logic and FK
CREATE INDEX [IX dbo.Child ParentID]
ON dbo.Child (ParentID);

    删除触发器

    CREATE TRIGGER dbo.Child_AD
ON dbo.Child
AFTER DELETE AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET XACT_ABORT, NOCOUNT ON;
    SET ROWCOUNT 0;

    IF EXISTS
    (
        SELECT DEL.ParentID
        FROM Deleted AS DEL
        EXCEPT
        SELECT C.ParentID
        FROM dbo.Child AS C
    )
    BEGIN
        RAISERROR ('Cannot delete the only child of a parent', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

    更新触发器

    CREATE TRIGGER dbo.Child_AU
ON dbo.Child
AFTER UPDATE AS
BEGIN
    IF @@ROWCOUNT = 0 OR NOT UPDATE(IsFavourite) RETURN;
    SET XACT_ABORT, NOCOUNT ON;
    SET ROWCOUNT 0;

    IF EXISTS
    (
        SELECT 1 
        FROM Deleted AS DEL
        JOIN Inserted AS INS
            ON INS.ChildID = DEL.ChildID
        WHERE 
            DEL.IsFavourite = CONVERT(bit, 'true')
            AND INS.IsFavourite = CONVERT(bit, 'false')
            AND NOT EXISTS
            (
                SELECT 1
                FROM dbo.Child AS C
                WHERE C.ParentID = INS.ParentID
                AND C.ChildID <> INS.ChildID
            )
    )
    BEGIN
        RAISERROR ('Cannot change IsFavourite to false for the only child of a parent', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

    和往常一样，如果主键不可变，更新触发器只会正确处理所有多行更新。实现此目的的最简单方法是创建ChildID标识列。

    • 2

相关问题