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.