Hi,
I'm trying to answer the following question...
"Explain the issues that arise when NULLs are present in columns that make up foreign keys. Discuss how ANSI have attempted to resolve this issue with the 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? I initially thought they were referring to OUTER JOINS, but I'm not sure anymore.
Any advice would be appreciated. Thanks.
If I remember right, these rules are about composite foreign keys. For example, consider an address table defined like:
deliveryaddressid - order - orderline - street - ...
Where (order,orderline) is a foreign key to the orderline table. The matching rules decide how joins behave when one part of the foreign key is NULL. For example, a row like:
32 - null - 1123 - 'Main Street 1' - ...
Here's an article about partial foreign keys (PDF download, 6 pages) The relevant part seems to be:
ANSI SQL 92 permits and databases such as Oracle support alternative matching rules for composite foreign keys, including:
• Match Full –Partially null foreign keys are not permitted. Either all components of the foreign key must be null, or the combination of values contained in the foreign key must appear as the primary or unique key value of a single row of the referenced table. [Default]
• Match Partial – Partially null composite foreign keys are permitted. Either all components of the foreign key must be null, or the combination of non-null values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of a single row in the referenced table.
• Match None – Partially null composite foreign keys are permitted. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.