Hi all;
Here is a brief guide to NULL's and Referential Integrity:
NULL is a special SQL value meaning 'unknown.' Well, it is a little more
complicated and NULL can mean "value does not exist." Therefore X = NULL is
NULL becuase we don't know if the NULL is equal to X. So:
NULL does not equal NULL
NULL does not equal anything else.
NULL does not equal an empty string
You get the picture.
Think of it this way: 2 non-existant or unknown values don't equal any
other existant or non-existant value, known or unknown.
Now, referential integrity is defined as follows:
For every non-NULL foreign key, there is a corresponding primary key in the
referenced table.
Note that NULL's are specifically handled in the RI definition.
When do I use NULL's? NULL's have a few uses:
1: To indicate that the foreign key does NOT correspond with a primary key
in the referencing table. In this case, NULL means something like "value
does not exist" (this is not the same as an empty value). In an employee's
table, a NULL in the manager's field would mean "Employee does not report to
any other employee as a manager."
2: To indicate that the value probably exists, but is unknown at present
(we don't know this customer's address, so we set it to NULL).
Best Wishes,
Chris Travers