Does Null or Not Null Make any Difference?

[kuau]

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

[GarethMc]

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);

[kuau]

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 ' '

[GarethMc]

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....

[kuau]

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.

[GarethMc]

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

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

[GarethMc]

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?

[Twey]

I read that php5 changed the definition of "empty."

PHP and SQL are entirely different languages. Their definitions are not linked in any way.