Enforce ONE to MANY relationship in SQL Server

I have 2 tables in my database. Table 1 'OrderItems' and Table 2 'Orders'. Each 'Order' can have zero or more 'OrderItems'. I defined a FK constraint linking the primary keys: OrderId and OrderItemId with the DeleteRule set to 'Cascade'. This ensures that all OrderItems will be deleted when I delete an Order.

What I need to avoid are empty Orders. I need to ensure that an Order has AT LEAST o开发者_运维知识库ne OrderItem, otherwise it should be deleted automatically as soon as the last linked OrderItem is deleted. I could of course check for this in my app, but ideally the db can deal with this.

I am using MS SQL Server 2008 and Entity Framework as my ORM.

Thanks!

---

Such a relationship is not enforceable using ordinary relational rules; after all, how would you add an order? If you can't add an order item without an order, but you can't have any orders without order items, you run into a chicken-or-the-egg scenario.

Your only real solution would be to create a delete trigger on the OrderItem table that deletes the corresponding order if it's the last item.

---

This bidirectional relationship may not be enforceable in the db schema. You probably will need to enforce this in your business logic.

---

I'm not aware of a constraint-based way to do this. You might have success with a trigger on deletes from the OrderItem table, that checks for "childless" Order records and deletes those... This may be a heavyweight "boil the ocean" approach, though, and you may be better off capturing this scenario in business logic, as @rcravens recommends.

---

Consider an UPDATE TRIGGER to check if you have any remaining order items and then archive the Order.

This will fire on every change to a row in the table so you should consider the logic and associated performance impact carefully.