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
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.