Enforcing Mandatory Parent/Child relationships
-
I'm new to this forum, so forget me if this is an old topic, but I can't find it using the search.
I've trying to figure out how to enforce what I call P relationships. That is a 1 to 1/many relationship. For example: (the classic) An ORDER must have at least 1 ORDER LINE. The FK in ORDER LINE enforces that it must have an ORDER, but how can I enforce that an ORDER has at least 1 ORDER LINE?? I've always done this in the application logic in the past, but I'd like a SQL solution. Does anyone know how to enforce this with Triggers or FKs or some SQL Server way?
Thanks, Muaddib
-
As far as I can think, this is best left to the application. There is no standard way of enforcing this - nor can I envisage one.
Of course, it would be possible to create a trigger that ensured that an ORDER_LINE entry was always created if an ORDER record was created, but what data would be on that ORDER_LINE row? If all of the data required to create the ORDER_LINE row comes from the ORDER row, there's no point having the ORDER_LINE row. If not, where is the extra data coming from?
-
True, I could have an ORDER Insert Trigger that creates a dummy ORDER LINE, but as you say, I wouldn't know what data to insert in the other columns.
I don't know how the application is using these tables, since I'm not part of the programming staff. I'm the DBA, and have been asked to enforce the relationships from the data model, which says 1 to 1/many. So I'm assuming that the data for both of these tables will be inserted at the same time. I'm hoping someone knows a "trick" or someway to enforce this rule.
-
Presumably the data model was created by the programmers, who knew when they were creating it that they would always create at least one ORDER_LINE row for every ORDER row? So they put 1-1/many on the relationship, based on their knowledge of how the app works. But you, as DBA, cannot easily enforce this - time to have a word with management, I reckon
Of course, you can mention how you have successfully enforced all of the other relationships at the same meeting
-
If you have an dbo.addOrderLine stored procedure then you could set up a dbo.addOrder stored procedure that accepts the parameters necessary to add an order and a single order line.
Wrap the whole lot up in a transaction
BEGIN Transactions
INSERT dbo.Orders etc
Grab the primary key of the newly created order.
INSERT dbo.OrderLine etc
If no errors then COMMIT TRANSACTION
otherwise
ROLLBACK TRANSACTION
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply