0
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 )

The first desire is that I would like to prevent the situation of childless parents. Obviously when the pair is first created the Parent will have no Child, and I don't want to prevent the insert itself; but I want to outright forbid prevent deleting the last child. (For business reasons Parents will never be deleted.)

As a second desire, I need to prevent more than one Child with IsFavorite=1, which I know I can do with a filtered unique index. I also would like to discourage having no Children with IsFavorite=1 -- i.e., don't permit setting IsFavorite=0 if this is the only Child of its Parent. (From the app side, inserts should normally be done correctly with IsFavorite=1 for the first child; but I don't want the database to interfere with that.)

I'm sure I'm not the first with these desires but I couldn't find a matching question on dba or on stackoverflow. If this is a dupe, by all means mark it dupe and point me where I need to be.

2

You could make (ParentId,FavoriteChildId) a FK on Parent and get most of this. However in SQL Server there's no practical way to make the relationship required, so you must allow a parent to have no favorite child, or else you wouldn't be able to insert a new parent without disabling the FK.

You can add a trigger to prevent later setting FavoriteChildId to null, but you must allow it at least in the case where a Parent has a single Child, as there would otherwise be no way to delete that Child. So I'm not sure it's worth bothering with the Trigger. EG:

--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
  • constraint pk_child primary key (ParentID, ChildID) does not ensure that the ChildID is unique, like my original schema does; that needs its own separate constraint. I don't mind allowing a newly inserted parent to have no favorite child, but I don't want to go from having a favorite to having no favorite. I guess your schema handles that. I'll do some testing. – Ross Presser Aug 2 '18 at 17:30
  • It handles my first desire but not my second. UPDATE Parent SET FavoriteChildId=NULL WHERE ParentID=1 is allowed. I guess I could prevent that with a trigger? – Ross Presser Aug 2 '18 at 17:36
  • I have a solution now, based on your help.. sqlfiddle.com/#!18/3d59f/1 The trigger catches UPDATEs where the FavoriteChildId is going from non-null to null. If you'll edit your script to resemble mine with extra UNIQUE and the trigger, I will accept your answer. – Ross Presser Aug 2 '18 at 18:46
2

It is perhaps easiest to implement these (somewhat unusual) requirements with a filtered unique index and a couple of AFTER triggers:

Tables and indexes

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);

Delete trigger

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;

Update trigger

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;

As usual, the update trigger will only handle all multi-row updates properly if the primary key is immutable. The easiest way to achieve this would be to make ChildID an identity column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.