List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:September 9 1999 12:58am
Subject:Re: strange NOT NULL values....
View as plain text  
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
Thread
strange NOT NULL values....WJ Gilmore9 Sep
  • Re: strange NOT NULL values....Thimble Smith9 Sep