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

One-to-one-or-more relationships, revisited

Regarding your answer to the question posed on One-to-one-or-more relationships on Nov. 13, 2002, there is a solution that would enforce referential integrity: the use of a subtype entity. For example, if there is an invoice table INVOICE(Invoice_Num PK) that stores all the invoice information, and you want to allow, but not require, details, you can create a second table for the details: INVOICE_DETAIL(Invoice_Num PK FK, Detail). This would avoid the problem of using a NULL as inapplicable, which shouldn't be done, or relying on triggers or application enforcement of integrity, which also shouldn't be done.

I don't know of any database engine that supports subtype entities through DRI.

While you are correct and many E-R products allow subtype entities, I don't know of any database engine that enforces them. Doing so would violate the Atomicity requirement of an ACID database, which would make the engine non-ACID as well as non-SQL compliant.

For More Information

This was last published in January 2003

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.