Null or Not Null... that is my Question!

New Member Joined: Jan 22, 2007 Posts: 20

Hi,

Can someone please tell me, When creating a new (MySQL) table, am I right in thinking that you would specify the field as 'NULL' if that field might or might not have a value?

E.g. - When a user fills in a form they might leave some fields empty... or they might not,
therefore in the table that stores the info, those fields should be specifed as 'NULL'.

If that makes any sence! Mr. Green

registered · Posted: Wed Jan 31, 2007 9:50 pm

I think that is correct. In practice though, I think most people now recommend the NOT NULL option. Having it NULL complicates your PHP logic and also uses slightly more space in the database. I'd love to hear what everyone else says on this.

Posted: Thu Feb 01, 2007 8:34 am

Ok Gremmie,

So that would be any 'Required' fields = Not Null... is that right?

Posted: Thu Feb 01, 2007 6:58 pm

I have never found a reason to use NULL fields. Strings can be empty, dates can be '0000-00-00', etc.

Posted: Thu Feb 01, 2007 7:34 pm

If you want an opinion, I agree Gremmie, I personally, like the idea of letting php catch the error of something being empty Long before Mysql does and returns an error, ecspecially if creating something use by others. I Just like the idea that the script does the checking, Making sure things are correct before inserted.

Posted: Thu Feb 01, 2007 10:40 pm

Me thinks we all need to go back and reread Codd and Date. If I am not mistaken you could find some very extensive and "deep" discussions about this topic and how it relates to database design.

Here's one quote I found in a brief Google on the topic:

Quote:

The limitation in existing implementations that generates the most comment from the various parties in the debate is the problem with ‘null’ values in relational databases. Put simply, a database field has a type (50 characters, for instance, or a floating point number to two decimal places, or an 8-bit integer), but when you don’t fill the field in (i.e. it’s ‘null’) it loses all its meaning. Even the ANSI standards state that if a field is null it’s said not to exist — so if you ask a database for “all entries where field X is not equal to 47” it won’t return any of those where field X is null because instead of saying “Null doesn’t equal 47” ,(sic) the value “null” is deemed not to be comparable with any non-null field.

This probably implies that we take Gremmie's approach but it's worth further research.

Posted: Fri Feb 02, 2007 12:56 pm

Well I'm just coming to it from the point of view of the PHP programmer. When I do a select * or something, I don't wanna have to check for NULL in addition to the empty string, for example. It just makes my life harder in the PHP layer.

I am weak on the database theory of things. I thought I picked up from somewhere that database people are now coming to the conclusion that NULL is also troublesome for their designs.

Posted: Fri Feb 02, 2007 6:06 pm

Thanks for everyones input on this!...

As fkelly suggested, I thought I'd embark on 'further research'. So off I went RTM

...

...after 'Googling' for several hrs and reading contradiction after contradiction, I came across this....

IBM Informix Guide to SQL: Syntax

Using NULL as a Default Value

If you specify no default value for a column, the default is NULL unless you place a NOT NULL constraint on the column. In this case, no default exists.

If you specify NULL as the default value for a column, you cannot specify a NOT NULL constraint as part of the column definition. (For details of NOT NULL constraints, see Using the NOT NULL Constraint.)

NULL is not a valid default value for a column that is part of a primary key.

If the column is a BYTE or TEXT data type, NULL is the only valid default value.

In Dynamic Server, if the column is a BLOB or CLOB data type, NULL is the only valid default value.

----------------------------------------------------------------------------------------------------------------------------

Using the NOT NULL Constraint

Use the NOT NULL keywords to require that a column receive a value during insert or update operations. If you place a NOT NULL constraint on a column (and no default value is specified), you must enter a value into this column when you insert a row or update that column in a row. If you do not enter a value, the database server returns an error, because no default value exists.

The following example creates the newitems table. In newitems, the column manucode does not have a default value nor does it allow NULL values.

CREATE TABLE newitems (
newitem_num INTEGER,
manucode CHAR(3) NOT NULL,
promotype INTEGER,
descrip CHAR(20))

You cannot specify NULL as the explicit default value for a column if you also specify the NOT NULL constraint.

----------------------------------------------------------------------------------------------

The way I read that, it would also seem to support Gremmie's approach Very Happy

Anyway... I thought it was only fair to let you all know what I'd gained from my day out with Google Laughing

must dash now though... I've got tables to build!!!

Posted: Fri Feb 02, 2007 6:52 pm

NULL vs NOT NULL is easy but MySQL <= 4.1 sucks in that.

NULL means "nothing" and that is different from "empty".
Basicaly it means that NULL is everything and nothing:
- a character field with a value of NULL is not a character
- an integer field with a value of NULL is not an integer
- a blob field with a value of NULL is not a blob

In programming languages like C there's some-thing(this word freaking bans me thanks to f***ing sentinel) like void this also means nothing and everything. It depends on what kind of value you give it, but takes it a little further because you never know what void contains (think about a NULL field type)

NOT NULL therefore explains that the column must contain a value of the specified type. Empty string, integer 0 or a file in the blob.

If you have specified that a column is NOT NULL you must specify a default value and either insert a value or DEFAULT.

Code:

INSERT INTO table (my_null_field, my_not_null_field) VALUES (NULL, DEFAULT)

A good practice is to always use NOT NULL columns so that you can pinpoint your bugs while developing your PHP scripts and make them nullable afterwards if suited.

However, in the world of webdesign NULL doesn't realy exist anyway. Afterall, for example, if you post form data the empty text fields are send which makes them empty and not null.
An unsubmitted form field is NULL and this only happens in radio buttons where none is selected.

So the conclusion is simple: all columns should be NOT NULL unless, they are not form submitted, then they may be either NULL or NOT NULL to use DEFAULT.

New Member Joined: Feb 02, 2007 Posts: 2

djmaze wrote:

... some-thing(this word freaking bans me thanks to f***ing sentinel) ...

Dj, it's not the word something as you can see it doesn't ban me. I'm using a dummy account so there's no chance of the system recognizing me as Raven nor as an admin.

Here is a post that I just tried.
http://www.ravenphpscripts.com/postp94236.html#94236

Raven

Posted: Fri Feb 02, 2007 8:58 pm

That's odd raven cos when i changed the word something into some-thing the black screen of death was gone.
Weird huh?

Maybe has to do with the full content?
Edit my above post and hit the preview button

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

I would agree - it's got something killing me

to do with the full message. This one ought to be fun to figure out. Thanks a lot speedtype