How to enforce that one row in a parent table MUST be associated with at least a row in a child table [duplicate]

Question

I have a one to many relationship enforced via FK, classic example order and order_item, whereby a fk on the order_item to the order table enforces that one item belongs to one order, and one order CAN have 1 or more item. I'd need to change the "CAN" to "MUST", basically any order must at least have one item associated with it. What would be the best way to enforce this. Many thanks

Answer 1

There is no declarative referential integrity to enforce one or more children in SQL Server. You have to enforce this with application logic.

Note too that if you are using a foreign key constraint on the child, you can't save the child until the primary key of the parent is known. This means that you need to have a transactional wrapper (BEGIN TRAN / COMMIT) around the insert of the parent and it's first child, so that the parent is not kept unless the child is also successfully inserted.