Sometimes it is difficult to implement business rules. Here is a case in point regarding integrity routines. Say you have a model with CUSTOMER, ORDER and ORDERITEM (and of course, ITEM). Say, also, a CUSTOMER can optionally place an ORDER, but an ORDER must have at least one ORDERITEM, and an ORDERITEM (correspondingly) must belong to an ORDER. In other words, an ORDER and an ORDERITEM are mutually required. We can not have an order without an item in it, and every ORDERITEM must belong to one order.
I think that the relation from ORDERITEM to ORDER is ensured by a referential integrity constraint, but that the inverse relation from ORDER to ORDERITEM must be done via a user written integrity constraint or trigger. If an ORDER is placed and does not have result in both an ORDER and an ORDERITEM, then the entire transaction (and any data created) must be rolled back. Is my thinking correct? Do you have any additional comments or details?
Your thinking is quite right. Yes, the child-to-parent relationship from ORDERITEM to ORDER can be enforced by a relational constraint, by declaring a foreign key in the ORDERITEM that references the primary key of the ORDER. This means you cannot add an ORDERITEM row without a parent ORDER row, and you cannot delete an ORDER row if it has ORDERITEM rows.
In the other direction, I'm not completely sure about this, but it seems to me that it may take a bit more fancy footwork than just having a user-defined constraint. Whatever the mechanism is to ensure the ORDER has at least one ORDERITEM, it must somehow be turned off during the "new order" transaction. You cannot, of course, add the ORDERITEM row before its parent ORDER row, but the moment you insert the new parent ORDER row, and before you get a chance to insert its first ORDERITEM child row, that ORDER row stands in plain violation of the constraint. So you have to turn whatever it is off, add the ORDER row, and insert at least one ORDERITEM row, before turning whatever it is back on.
Beyond this, I'm afraid I don't have a lot to offer in the way of advice. Implementing user-defined constraints depends too much on the nuances of the particular database system you're working with. My gut feel in this case would be not to create a user-defined constraint in the database at all, but rather, to implement the business rule in the application logic instead. Yes, I realize I will get yelled at by modelling purists, and perhaps by DBAs of one persuasion or another who know how to do it for their particular database system...
To my mind, inserting a new ORDER and its ORDERITEM rows into a database is probably not a general or widespread function, and therefore there is little to be gained by abstracting the business rule into the database, since there will likely be only one program or block of code (the "new order" logic) that will call it.
There will also typically be only one program or block of code to delete an ORDERITEM. This block will need to add a check that if the last ORDERITEM is deleted, then the ORDER row must be deleted too. Some might argue that if a user deletes the last ORDERITEM, the ORDER should not just disappear, but rather, hang around in case the user decides to add a new ORDERITEM. This behaviour seems more forgiving to me, more user-friendly, and therefore it would completely obviate the need for the constraint in the first place.
This was first published in November 2003