• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

advantages of "NOT NULL"

 
paul wheaton
Trailboss
Pie
Posts: 21880
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if we create a table and specify that a field is "NOT NULL", there is the obvious advantage that you cannot insert a row or modify a row in such a way that that field is null. You have a gurantee of data at the database level.
But are there other advantages?
I'm looking at some code where users want to cut back on what is required - so the "NOT NULL" stuff is about to be yanked.
But I'm thinking that the engineer before me was a good engineer. Maybe there were other reasons for using "NOT NULL" as often as he did. Is there some great optimization that is gained by using NOT NULL?
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe he was just too lazy to write his code to check for nulls. You can always go to not null with default if using nulls might cause problems with application code.
 
paul wheaton
Trailboss
Pie
Posts: 21880
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the old days when designing the database first, you would want to put "not null" in to make sure that some putz didn't accidentally leave it out. But that usually isn't the case any more.
So it got me wondering if some databases can optimize somehow on something like that. Not so?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've found that by putting unnecessary not null constraints on columns, that users will just fill it up with garbage if they don't have a real value( i.e. our database is littered with birthdates of 00000000 and social insurance numbers of 000000000 ) We'd have been better off leaving them ( and many others ) as nulls as they wouldn't be occupying space in the DB.
If you are ridding yourself of the NOT NULL's, it can be a long and tedious task (depending on the amount of code and number of columns effected) to modify all your DB access code to "nicely" handle all the new nulls.
Jamie
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most databases can optimize on nulls. I have never heard of a database that can optimize on not null.
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not quite.
Having a NOT NULL means that all rows can be stored in a b-tree index. this means that if you search on that column then you can find the rows without a tablescan.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may start to find some of your queries that were once reliable may produce undesirable results when you start allowing null values:
Example - table --> emp
id Country
-- ---------
1 Canada
2 US
3 <null or default value>
select id from emp where country not in ( 'Canada' )
if row number 3 is a default value ( NOT NULL ) then the results are:
2 US
3 <default value>
if row 3 has a country value of null, then the results are different:
2 US
The null values are not included in the results when you use NOT IN, NOT LIKE, <, >, etc.
Just a heads up...
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic