Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Does Null or Not Null Make any Difference?

  1. #11
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default

    Great explanation! Thanks very much. "I see, said the blind man."

  2. #12
    Join Date
    Jul 2008
    Location
    Johannesburg, South Africa
    Posts
    31
    Thanks
    1
    Thanked 10 Times in 10 Posts

    Default

    Ummm .. one thing to remember which I think people forget...

    By setting a field to NOT NULL it also means that you MUST insert some value in that field and will throw an error if there is no value submitted...

    A field without NOT NULL set means that you can skip adding a value for that field. i.e. you may have a table with three fields, Name, Address and Email and Name is set to NOT NULL, Address does not have NOT NULL set and Email does have NOT NULL set... the two inserts are legal:

    INSERT INTO table (Name, Address, Email) VALUES ('namevalue' , 'addressvalue','emailvalue');

    INSERT INTO table (Name, Email) VALUES ('namevalue', 'emailvalue');

    The next one would not be legal as NOT NULL means the fields Name and Email cannot be nothing (null):

    INSERT INTO table (Address) VALUES ('addressvalue);

  3. The Following User Says Thank You to GarethMc For This Useful Post:

    kuau (07-21-2008)

  4. #13
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default

    Hmmm, I didn't know about that aspect. Do you mean you can't have an empty field? I could swear I have lots of blank fields in my null columns. But I haven't done the data entry form yet. Will that cause problems if people leave a filed blank?

    What about this...

    INSERT INTO table (Address) VALUES ('namevalue', 'addressvalue', 'emailvalue');

    where the values are ' '

  5. #14
    Join Date
    Jul 2008
    Location
    Johannesburg, South Africa
    Posts
    31
    Thanks
    1
    Thanked 10 Times in 10 Posts

    Default

    Thats a value of ' ' .. which is not a null value hehe .. it does take getting ure head around it though.

    If your field does not have the NOT NULL flag set in the table definition then it can accept a NULL value .. ' ' is a space .. even '' is accepted because that is an empty string which isn't null....

    I know . gets a bit heady... but null is only assigned to a field when you don't assign anything at all to it as in:

    INSERT INTO table (Address) VALUES ('addressinfo')

    Name and Email will be null because nothing is assigned to it which will result in error because those fields are set with the NOT NULL flag in teh table definition meaning they cannot be NULL. This is useful if you have fields that MUST have a value..

    Oh and

    INSERT INTO table (Address) VALUES ('namevalue', 'addressvalue', 'emailvalue');

    will result in a SQL syntax error because the number of fields you are trying to assign to do not match the number of values passed....

  6. The Following User Says Thank You to GarethMc For This Useful Post:

    kuau (07-21-2008)

  7. #15
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default

    Sorry, I meant
    INSERT INTO table (Name, Address, Email) VALUES ('namevalue', 'addressvalue', 'emailvalue');

    I was focused on the last bit. I read that php5 changed the definition of "empty." Is a field "empty" if it is full of null? Is it null if it had a value and then you delete that value? Apparently a value of zero can be considered empty in some instances (if I read it correctly). Pretty confusing.

  8. #16
    Join Date
    Jul 2008
    Location
    Johannesburg, South Africa
    Posts
    31
    Thanks
    1
    Thanked 10 Times in 10 Posts

    Default

    Ok sorry or double post but I realised it migh tmake no sense my previous post so I'm taking a different tack >.<

    Using my little three field table as an example you can use the following SQL to create it

    Code:
    CREATE TABLE playfultable (Name VARCHAR(50) NOT NULL, Address VARCHAR(150), Email VARCHAR(50) NOT NULL);
    Notice how the Name and Email fields have the NOT NULL flag set on creation. This means these fields are compulsory and not inserting a value will cause an error...

    Doing a DESC playfultable:

    +---------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+--------------+------+-----+---------+-------+
    | Name | varchar(50) | NO | | NULL | |
    | Address | varchar(150) | YES | | NULL | |
    | Email | varchar(50) | NO | | NULL | |
    +---------+--------------+------+-----+---------+-------+

    Under the NULL column it says NO .. i.e. they can't be NULL ever

    Hope that explains better

  9. The Following User Says Thank You to GarethMc For This Useful Post:

    kuau (07-21-2008)

  10. #17
    Join Date
    Jul 2008
    Location
    Johannesburg, South Africa
    Posts
    31
    Thanks
    1
    Thanked 10 Times in 10 Posts

    Default

    Quote Originally Posted by kuau View Post
    Sorry, I meant
    INSERT INTO table (Name, Address, Email) VALUES ('namevalue', 'addressvalue', 'emailvalue');

    I was focused on the last bit. I read that php5 changed the definition of "empty." Is a field "empty" if it is full of null? Is it null if it had a value and then you delete that value? Apparently a value of zero can be considered empty in some instances (if I read it correctly). Pretty confusing.
    NULL is no value .. thats all .. nothing ever existed or exists there.. has nothing ... 0 is not nothing .. 0 IS a value of zero.... '' is not nothing its an empty string... NULL is nothing... simple isn't it?

  11. #18
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    I read that php5 changed the definition of "empty."
    PHP and SQL are entirely different languages. Their definitions are not linked in any way.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •