Oracle - NULLS in foreign keys?
I am trying to answer the following question ...
"Explain the problems when NULLs are present in columns that make up foreign keys. Discuss how ANSI tried to solve this problem with three" matching rules "that can be adopted when using concatenated foreign keys."
Can anyone point me in the right direction as to what these "matching rules" are? At first I thought they were referring to OUTER JOINS, but I'm not sure anymore.
Any advice would be appreciated. Thanks.
If I recall correctly, these rules are for composite foreign keys. For example, consider a table of addresses defined as:
deliveryaddressid - order - orderline - street - ...
Where (order, order) is the foreign key to the ordered row table. Matching rules determine how joins behave when one part of a foreign key is NULL. For example, a string like:
32 - null - 1123 - 'Main Street 1' - ...
Here's an article on partial foreign keys (PDF download, 6 pages). The relevant part looks like this:
ANSI SQL 92 and database permissions, such as because Oracle supports alternate matching rules for composite foreign keys, including:
• Match complete - completely zero foreign keys are not allowed. Either all foreign key components must be null, or the combination of values contained in the foreign key must appear as the primary or unique key value of one row of the referenced table. [Default]
• Match Partial - Partially zero composition allowed foreign keys. Either all of the foreign key components must be null, or the combination of nonzero values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of one row in the referenced table.
• No match- Partially null compound foreign keys are allowed. If any column of the composite foreign key is zero, then the non-zero portions of the key do not have to match the portion of the parent key.
a source to share