This post is for csn, who posted about how unique keys and NULLs interact.
NULL can be a biotch! For example, I want to create a unique constraint on a table:
UNIQUE (name, site_id, parent_id)
But parent_id can be NULL. So, this constraint allows:
abc|1|null
abc|1|null
def|1|null
def|1|null
...
Know how to make this work, Sxooter?
I also occasionally get bit by NULL in where clauses with AND's.
Ok, I'll take this in two parts. The most common misconception of a NULL is that it is the same as zero for an integer, or a blank field of length zero in text types. It's neither. For any type, a NULL means we don't know. There's some argument that one NULL is not enough, and we should have multiple types. There are a lot of ways of saying "we don't know." But they all have one thing in common. They're not the same as zero / blank.
For instance, if your bank account is at 0.00 and you KNOW you're broke, then you would say it's at zero. But if it's at 0.00 or 1,000,000.00 and you don't know, both are NULL, to you.
So, when you create a primary key, and leave part of it nullable, you are saying that it's OK to have two or more records with the same fields being unknown at the same time, and the others matching. You database isn't a mind reader so it can't refuse the record until you try to put illegal values in those fields. If having NULLs in this field is a bad thing for you, then you are probably better off setting the fields to not null.
create master table (id int autoinc NOT NULL);
create table test (position int, class text, , primary key (a.b));
insert into test values (NULL,'A');
insert into test values (NULL,'A');
insert into test values (12,NULL);
insert into test values (12,NULL);
insert into test values (NULL,NULL);
Are all legal, because any of those NULLs could be ANY value. We don't know, so we assume they're not the same.
The other issue you mention is about the propagation of NULLs. If I ask you what 10 times some unknown quantity, the answer is unknown. so, 10*NULL = NULL. Those are two different NULLs.
With AND, we're normally looking at a this:
FALSE and (anything else) = FALSE
TRUE and TRUE = TRUE
So, if we replace one of those at a time, we get this:
FALSE AND NULL = FALSE, because no matter what NULL is, it can't make the output anything but FALSE.
TRUE AND NULL = NULL, because the NULL field could be either TRUE or FALSE, hence the output is unknown.
With ORs, we get the opposite:
TRUE OR (anything else) = TRUE
FALSE OR FALSE = FALSE
So, we get these two:
TRUE OR NULL = TRUE
FALSE OR NULL = NULL
With multiple inputs into an OR, ANY TRUE makes the output TRUE, and with an AND, any FALSE makes the output FALSE. Everything else propagates out NULLs.
