Problem solve Get help with specific problems with your technologies, process and projects.

One-to-one-or-more relationships

How should one enforce/implement one-to-one-or-more relationships? For example, suppose I want enforce the rule that an Invoice carries one or more, i.e. not zero, Invoice Items. Most DB products seems to support only one-to-zero-or-more constraints natively.

The problem with this kind of relationship (1 to 1 or more) is due to a fundamental difference between business constraints and logical constraints. The business world can have objects like invoices where a valid invoice must have at least one detail line. Even in this case, an empty invoice still has to exist at least momentarily after creation except in a seriously contrived example. Relational databases are based on set theory, and the empty set is a subset of every real set. There are theoretical sets where this isn't the case, but no set can exist in the real world that doesn't have the empty set as one of its subsets.

From the standpoint of DRI (Declarative Referential Integrity), I don't know of any way to prevent an invoice without a detail line without denormalization of the tables.

My first question when presented with a case like this has to be since any logical design has to permit an invoice without detail lines in order to allow invoices to be created, how much work are you willing to do to make life difficult? An invoice without detail lines is essentially a Null invoice, so it shouldn't hurt anything.

In my opinion, the best way to deal with this is to have the client application manage the issue for you. If no detail lines are written in the invoice creation, just rollback the "new invoice" transaction. There are products such as ErWin that will "automagically" write triggers to support this kind of cardinality, but I see them as problematic instead of helpful due to the logical limitations that these triggers imply.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.