|
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.
|
