Scott Hollows – One Line At A Time

I’ll show you how to break them, and how to fix them

Im not talking about DISABLED or DEFERRED or NOVALIDATE foreign keys – No, this is different.

This is for a specific scenario – a multi column foreign key based on one or more optional columns

You can insert a row with junk values in the foreign key if :
1) it is a composite (multi column) foreign key
2) and at least one of the FK values has a null value.

See this demo
Download

Not A Bug

This is not a bug. This is the intended, documented behaviour and is part of the ANSI SQL standard.

The Oracle SQL manual says “To satisfy a composite foreign key constraint … or the value of at least one of the columns of the foreign key must be null.”

So its not technically “broken”, however Im sure you dont like it because your data quality is at risk due to this unexpected feature.


Lets fix this

Fortunately, there is a simple workaround

Create a check constraint (or trigger) that verifies all of the FK columns in a row have a value, or none of them have a value.

ALTER TABLE ADMIN.EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK_CHK
CHECK (
/* validate optional multi-column foreign key */
(ORG_ID IS NULL AND DEPT_ID IS NULL)
OR
(ORG_ID IS NOT NULL AND DEPT_ID IS NOT NULL)
);

Sorry about the indent formatting. WordPress formatting is terrible

You can automate this with my code generator …

download SQL*Plus scripts

• demo_create_tables_and_insert_corrupt_data.sql
• fk_check_code_generator_ALL.sql
• fk_check_code_generator_DBA.sql
  – use this if you have access to DBA dictionary views
  
  Run the “fk*” scripts SQL*Plus
  It will show the problematic foreign keys
  and also generates the check constraint SQL for you.|
  

Scott Hollows

• Oracle & Delphi software developer based in Perth, Western Australia
• Australian Oracle User Group – National Committee Member
• Australian Delphi User Group – National Committee Member