This question already has an answer here:

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

marked as duplicate by ypercubeᵀᴹ, Max Vernon, Philᵀᴹ, RLF, Paul White sql-server May 9 '15 at 23:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • I think you'll need to do this via triggers. – Vérace May 9 '15 at 20:40

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.