CREATE TABLE ChildTypes(
	Id int Primary Key,
	Label varchar(50) not null,
	HasChildTable bit not null
)

INSERT INTO ChildTypes
SELECT 1,'First Child','True'
UNION ALL
SELECT 2,'Second Child','True'
UNION ALL
SELECT 3,'Something without children','False'

GO


CREATE TABLE Parents(
	Id int Primary Key,
	ChildTypeId int NOT NULL References ChildTypes(Id),
	Label varchar(50) NOT NULL,
	CONSTRAINT Testcon UNIQUE(Id,ChildTypeId)
)

GO

CREATE TABLE FirstChildren(
	Id int Primary Key references Parents(Id),
	ChildTypeId AS 1 Persisted References ChildTypes(Id),
	OtherStuff varchar(50),
	Foreign Key (Id,ChildTypeId) References Parents(Id,ChildTypeId)
)
GO

CREATE TABLE SecondChildren(
	Id int Primary Key references Parents(Id),
	ChildTypeId AS 2 Persisted References ChildTypes(Id),
	ReallyDifferentAttributes varchar(50),
	Foreign Key (Id,ChildTypeId) References Parents(Id,ChildTypeId)
)
GO
--create constraint where one child exists for each entry in parent table. Could use triggers? Not sure how I should construct these

GO
--insert some test data in, need constraint to allow this
INSERT INTO Parents(Id,Label,ChildTypeId)
SELECT 1,'test',1
UNION ALL
SELECT 2,'test2',2
UNION ALL
SELECT 3,'test3',3


INSERT INTO FirstChildren(Id,OtherStuff)
SELECT 1,'some data'


INSERT INTO SecondChildren(Id,ReallyDifferentAttributes)
SELECT 2,'some really different data'


GO
--test condition in which constraint should stop query, when removing child information without changing the childtype to 
--something that has no child
BEGIN TRAN
	DELETE FirstChildren
COMMIT TRAN


GO
--Test condition in which constraint would pass query, when changing the childtype of the object.
--I guess constraint would have to work after the whole batch has been completed
BEGIN TRAN
	DECLARE @IdToBeChanged int
	SET @IdToBeChanged=2

	DELETE SecondChildren WHERE Id=@IdToBeChanged

	UPDATE Parents
	SET ChildTypeId=1
	WHERE Id =@IdToBeChanged

	INSERT INTO FirstChildren(Id,OtherStuff)
	SELECT @IdToBeChanged, 'now a first child'
COMMIT TRAN



GO
--clean up
DROP TABLE FirstChildren
DROP TABLE SecondChildren
DROP TABLE Parents
DROP TABLE ChildTypes

quote:

---

Originally posted by michael.appleton

It's strange, this requirement must be so common, I'd expect something as mature as SQL, and SQL Server in particular, would have an accepted, simple solution, perhaps via multi-updates.

---