Prevent deleting last child 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.
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  ) &gt; 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