Impedir a exclusão do último filho

Você pode fazer (ParentId,FavoriteChildId) um FK em Parent e obter a maior parte disso. No entanto, no SQL Server, não há uma maneira prática de tornar o relacionamento necessário, portanto, você deve permitir que um pai não tenha filho favorito, ou então não poderá inserir um novo pai sem desabilitar o FK.

Você pode adicionar um gatilho para evitar definir FavoriteChildId como nulo, mas deve permitir isso pelo menos no caso de um pai ter um único filho, caso contrário não haveria como excluir esse filho. Então não tenho certeza se vale a pena se preocupar com o Trigger. POR EXEMPLO:

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

Talvez seja mais fácil implementar esses requisitos (um tanto incomuns) com um índice exclusivo filtrado e alguns AFTERgatilhos:

Tabelas e índices

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

Excluir gatilho

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;

Atualizar acionador

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;

Como de costume, o gatilho de atualização só lidará com todas as atualizações de várias linhas corretamente se a chave primária for imutável. A maneira mais fácil de conseguir isso seria criar ChildIDuma coluna de identidade.