NULL vs NOT NULL in database
A designer asked whether it's wise to declare every column NOT NULL and store spaces instead of NULLs, supposedly to avoid join problems. Respondents advised against a blanket rule: you lose the ability to distinguish a genuine blank from "unknown", you add overhead converting NULLs to spaces (noticeable on bulk loads), and padding numerics/dates with dummy values (e.g. 0 or 12/31/1899) causes worse problems. Consensus: primary/foreign key columns should always be NOT NULL, but other columns should allow NULLs; if keys could be blank, the design is wrong. One poster suggested NVL() in join predicates as an alternative. No single formal resolution beyond this advice.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there any advantage/disadvantage to setting all columns to "not null" and fill with "space" to avoid problems with table joins? We are redesigning a database and have been told to set all columns "not null" and set to space when no value exist. Does any one see any gotchas?
What problems with table joins? The only thing that's going to be a possible problem is the overhead of the not null constraints and setting null columns to all spaces. That will take some small amount of time, but when added together (such as during a bulk load) it adds up fast. In article <8knqnc$acm$1@news.xmission.com>, "Phelps, Mary" <mphelps@safetycenter.navy.mil> wrote: > > Is there any advantage/disadvantage to setting all columns to "not null" and > fill with "space" to avoid problems with table joins? We are redesigning a > database and have been told to set all columns "not null" and set to space > when no value exist. Does any one see any gotchas? > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
The main problem is detecting the difference between a "REAL" blank value and one that indicates a NULL. Do you care? Even if you do not today, you may in a month, things like this are inevitable. Art S. Kagel "Phelps, Mary" wrote: > > Is there any advantage/disadvantage to setting all columns to "not null" and > fill with "space" to avoid problems with table joins? We are redesigning a > database and have been told to set all columns "not null" and set to space > when no value exist. Does any one see any gotchas?
>The main problem is detecting the difference between a "REAL" blank >value and one that indicates a NULL. Do you care? Even if you do not >today, you may in a month, things like this are inevitable. Murphy's law ? But I have an example that it seems better to put spaces instead of null: If this field is a key (usually of a composite key), null value can be a problem in order to make some joins ... in the other hand, you have to convert explicitly the null value to spaces before recording, and that is so nasty and annoying ... Regards, Manel Falcó, On Mon, 17 Jul 2000 10:33:29 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: > >Art S. Kagel > >The main problem is detecting the difference between a "REAL" blank >value and one that indicates a NULL. Do you care? Even if you do not >today, you may in a month, things like this are inevitable. >"Phelps, Mary" wrote: >> >> Is there any advantage/disadvantage to setting all columns to "not null" and >> fill with "space" to avoid problems with table joins? We are redesigning a >> database and have been told to set all columns "not null" and set to space >> when no value exist. Does any one see any gotchas?
"Manel Falcó i Aige" wrote: > > >The main problem is detecting the difference between a "REAL" blank > >value and one that indicates a NULL. Do you care? Even if you do not > >today, you may in a month, things like this are inevitable. > Murphy's law ? > > But I have an example that it seems better to put spaces instead of > null: > If this field is a key (usually of a composite key), null value can > be a problem in order to make some joins ... > in the other hand, you have to convert explicitly the null value to > spaces before recording, and that is so nasty and annoying ... True, primary key and foreign key columns should ALWAYS be NOT NULL. On the other hand I cannot see why these columns would not have a value other than NULL or SPACES in the first place. If so then you are using the wrong columns as keys. Art S. Kagel > Regards, > Manel Falcó, > > On Mon, 17 Jul 2000 10:33:29 -0400, "Art S. Kagel" > <kagel@bloomberg.net> wrote: > > > > >Art S. Kagel > > > >The main problem is detecting the difference between a "REAL" blank > >value and one that indicates a NULL. Do you care? Even if you do not > >today, you may in a month, things like this are inevitable. > >"Phelps, Mary" wrote: > >> > >> Is there any advantage/disadvantage to setting all columns to "not null" and > >> fill with "space" to avoid problems with table joins? We are redesigning a > >> database and have been told to set all columns "not null" and set to space > >> when no value exist. Does any one see any gotchas?
I think 1 thing you should definitely avoid is making everything "not null" just because you heard it was a good idea. I have seen people do this and then jump thru extreme hoops to set numerics to 0 and char fields to " " - they even went so far as to set dates to 0 (12/31/1899 - now I understand why those places needed to print 4 digit years on reports....noone is going to think their financial transaction took place 100 years in the past or future when they see it on paper). NULL is a very special value and actually means something (a null FAX number would indicate there was no fax # or perhaps you just didn't know it), and primary keys are very important values...the 2 don't mix - if your primary keys can be null, I think you've got a design problem. A null transaction date in a banks database? Sounds sort of scary to me. "Phelps, Mary" wrote: > Is there any advantage/disadvantage to setting all columns to "not null" and > fill with "space" to avoid problems with table joins? We are redesigning a > database and have been told to set all columns "not null" and set to space > when no value exist. Does any one see any gotchas?
On Fri, 14 Jul 2000 15:24:16 -0400, "Phelps, Mary" <mphelps@safetycenter.navy.mil> wrote: > >Is there any advantage/disadvantage to setting all columns to "not null" and >fill with "space" to avoid problems with table joins? We are redesigning a >database and have been told to set all columns "not null" and set to space >when no value exist. Does any one see any gotchas? Exist new function NVL. It is possible (speed?) use it in joins? WHERE NVL(column1, "blank") = NVL(column2, "blank") Warning: my english is poor.