NOT NULL default '' vs default NULL

[gamefreak13]

I didn't see a MySQL section, so I posted this here (hope the mods don't mind).

 

What is the difference/better/pros/cons of "default NULL" and "NOT NULL"?

 

CREATE TABLE `mytable` (
  `userid` int(5) unsigned NOT NULL auto_increment,
  `firstname` varchar(100) default NULL,
  `lastname` varchar(100) NOT NULL default ''
  PRIMARY KEY  (`userid`)
) ENGINE=MyISAM ;

 

Also, do I have to specify the varchar length?

[minidak03]

First off yes varchar length is required.

 

Second off there are lots of benefits of having NULL and NOT NULL for instance I always use NULL but not always haha (Talk about a contradiction).

 

Lets say you have a database table with 3 fields which are A, B, and C.

 

If you require field A and B to have data but sometimes you need to leave C empty then for C you would use NULL otherwise that field can never be empty and will probably cause an error or issues.

 

Now lets say that I have another table with 3 fields which are D, E, and F and I place data in D and E but then F cannot be empty no matter what, lets say I'm using this value to determine the active status of a user where 0 means they are not activated and 1 means they are.

 

So if I added a new user I always want that new user to be not active until I activate there account so if field F was my activation status field I would use NOT NULL with a default of 0 this way if I forget to add this value when doing my SQL statement it won't matter because it will default to 0 which in this case means the users status will not be active.

 

There are tons of practical uses for each and I would suggest just thinking it through if you think you'll need to use NOT NULL with a default value then use it otherwise use NULL which means if other entires in that row has values one is allowed to be empty.

[gamefreak13]

I think in my case I can allow for all my fields to be NULL. I have:

 

(table: members) userid, username, password, email, firstname, lastname

 

All are required and all are inserted and checked by the php code. So I would think it would be safe to let the default be NULL since they'll all be set anyway.

 

However, on this one some fields I should probably default to something..

 

(table: logger) uname,  ip,  date, time, referer

 

The only field that might not be filled in is the referer since my php script checks to make sure the uname (username) is present or else it doesnt execute the query. And the ip date and time will always exist.

 

So with that said, I can set "default NULL" to every field except referer, which I could do something like "NOT NULL default 'Referer missing'" or of course just "NOT NULL default ''".

 

Just want to make sure I understand it right.

 

Oh, and what would be the outcome of SELECTing a field that is null? For example, "SELECT biography FROM mytable WHERE userid=1". If biography was null for userid 1, what would the script return? I would guess that it would return a php or mysql error?

[minidak03]

Yes you have all of that right and as for your last question of what would it return well 9 times out of 10 it wouldn't return anything at all because its a NULL value it would be like saying

 

$value = '';

 

Now in the past (Haven't seen this happen in decades maybe it was an old time bug that got worked out) that when using SQL (Note I didn't say MySql But SQL) the output would actually be the text NULL, however with MySql it doesn't return anything and there is no error to worry about because its just a blank field.

 

Maybe that difference is still around I don't know its been ages since I used SQL over MySql but if your wondering the difference there is but just think of MySql which is mainly used in Linux (PHP) based applications where SQL Windows (ASP) is mainly used for things like ASP.NET or windows applications.

 

I might get some flack for saying that but thats just how I see the main difference between SQL and MySql even though there are a lot of differences.

[gamefreak13]

So.. then they are the same thing. Right?

 

If NULL is like saying $value = ''; then thats the same as NOT NULL default '';.

 

I see no negative impact by setting the default to NULL. I mean, if I don't enter any info, of course its going to be empty (aka null).

 

Empty = null = ''

 

Or am I missing something?

[beboo002]

In any database null means garbage value when u define column level (not null) then it means u should enter some value but when u set default then it has pass some value to column .in every database null means something ratherthen zero or empty

[fenway]

So.. then they are the same thing. Right?

Not at all.

 

NULL means an undefined value -- for example, if you ask someone to fill out a form with the question "how many brothers do you have?", and make the question optional, then you need a way to differentiate between "I have no brothers" vs. "I didn't answer the question".  This is the difference between 0 and NULL; the same can be said for a question like "favourite pet", where it would be the different between '' (no such pet) and NULL (I didn't answer).

 

Hope that helps.