At 20:26, 19990908, WJ Gilmore wrote:
>mysql>create table tester (
>>name char(25) NOT NULL
>>);
>
>mysql>INSERT INTO tester VALUES("NULL");
>mysql>INSERT INTO tester VALUES("");
>
>both insertions executed okay, and the values "" (empty space) and NULL
>were inserted.
Jason, you're getting confused about what NULL is. NULL is a special
value in SQL. It's an "exceptional" value, because no matter what type
you are considering (integer, char, blob, date, etc.), the value NULL is
*never* a valid value for that type.
In the above examples, you inserted two valid character strings into
your table. The first string consisted of four characters, and the
second consisted of zero characters. But they are both valid character
strings.
If you did the following, you'd get the error you're looking for:
mysql> INSERT INTO tester VALUES (NULL);
ERROR 1048: Column 'name' cannot be null
Notice that the word NULL is not quoted. It is like using the word
SELECT - it's a special word in SQL, and is not in quotes. Quotes are
used to make string constants.
All that being said, you should do your best to stay away from using
NULL values in your programs, unless they are really appropriate. My
reason for saying that is that you have to explicitly account for NULL
values in all of your queries. For example, say you have a table with
the values:
Foods
ColumnA ColumnB
======= =======
One Apple
Two Butter
Three Cheese
Four NULL
Five Flour
Six NULL
Seven Seaweed
You could do the following queries:
SELECT * FROM Foods Where ColumnB = 'Butter';
=> Two Butter
SELECT * FROM Foods Where ColumnB <> 'Butter';
=> One Apple
Three Cheese
Five Flour
Seven Seaweed
What happened to Four and Six? Since they have the NULL value for
ColumnB, they are neither equal nor not-equal to 'Butter'. They're
NULL, unknown, incomparable. If you want to get them back, you have
to do this:
SELECT * FROM Foods Where ColumnB <> 'Butter' OR ColumnB IS NULL;
=> One Apple
Three Cheese
Four NULL
Five Flour
Six NULL
Seven Seaweed
Tim
