Re: why the need for is null? - Mailing list pgsql-general

From Chris Travers
Subject Re: why the need for is null?
Date
Msg-id 00fa01c3d127$89564d10$1f00053d@winxp
Whole thread Raw
In response to why the need for is null?  (Baldur Norddahl <bbn-pgsql.general@clansoft.dk>)
List pgsql-general
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


pgsql-general by date:

Previous
From: "Chris Travers"
Date:
Subject: Help with PostgreSQL porting project
Next
From: Enver ALTIN
Date:
Subject: Re: Help with PostgreSQL porting project