Null Value Fields vs. Not Null Value Fields

[FoxT2]

Hello,

Can someone explain to me what situations I would want to specify my fields in a table for null values. And what situations I would want to specify my fields as not null values. It seem that when I do not have null values checked it errors when I try to update the table saying that it cannot insert the null values. Then when I do have the null values checked it tells my invalid use of a null value. I cannot win either way.

Thanks,

FoxT

 

[SQLSister]

In general, it is best to avoid the use of null values as much as possible. However, not all fields have data in them that can be provided during the initial data entry (Such as the ship date for an item ordered). The fields which are not required and which cannot be filled in immediately need to allow nulls.

As far as handling the situation programically. First, ensure that your user interface does validation checks and a record is not submitted for insert unless all fields which are required have been filled in. Also ensure that any SQL you write to do inserts includes all the required fields.

 

[FoxT2]

Thanks for the reply, that helps a bit. Another question...

Say I have a table with these fields (None are null);

SSno
Name
Title
Address
City

some employees will have a title, other will not, how do update and add records to a table like this. I need the title field to be blank in some situations. Does non null field values mean that there always has to be a value in them?

FoxT

 

[jsteph]

If I may comment...I don't think it's a bad idea to have Null fields. Null tells you something--that the field's value has not been defined. What about an Employee table field 'termination date'? You can't initialize that to anything, and you certainly don't want to give it some moving-target-pretend-null value like 1/1/1900.

For the Title field, you *could* have an empty string, but this causes confusion, in my opinion, since now you've got to remember whether you check for is null or "", if the field accepts nulls.

BUT... what about numeric and date fields? These you must allow nulls, or else you're chasing some developer-defined 'null' placeholder to compare to see if it's empty, such as 1/1/1900 for dates or 0 for numerics, which in my opinion, is bad practice.

Null is Null--For example, the employee table field "termination date". What do you put for the current employees? If you init to some low value such as 1/1/1900, now any report that asks for 'all employees terminated before [some date], will return all current employees. I could name dozens of valid examples where a field may simply not apply for certain records--and maniacal 'normalization purists' may argue that that should never be the case, but we're here in the real world.

If you allow nulls, then you never have to wonder "hmmm, in this field, what means null...is it zero, empty string, or 1/1/1900, or 'N/A', etc??? That gets tedious.
Just my opinion
--Jim

 

[Pattycake245]

FoxT2,

You will have to ensure that your "Title" field allows nulls, otherwise you will not be allowed to do updates if the title is missing.

If you need to alter the table design to make "title" accept null values then:

alter table tablename
alter column title char(10) null

Tim

 

[FoxT2]

Thanks Jim for your comments. You have very good points for null vs. not null. If I setup a table to allow null values can I store "" to a field (no spaces)? If so is that considered null anymore? In databases like MS Access and FoxPro you can add new records and only filling in a few of the fields. In FoxPro if a field is defined not null you can add records with "" or just not add anything to the field at all and accepts the empty.

FoxT

 

[NeilTrain]

i agree with Jim, i used to be anti-null because thats what all the wrox sql books said, heh, but now that ive worked on some real world projects, Null is my friend.

 

[jsteph]

In Access, you could specify 'Allow Zero Length' on char fields. You could also allow or not allow nulls, which made for a complicated set of rules--it could be '' or Null, and they are both very different.

If you allow null then you don't need to set a default value, it just stays null if you omit this field in data-entry.

Another case for having nulls--try any Aggregate (Group By) query and use Sum or Avg, etc. If you have a field, say Age, and you have 10 people, but only know the ages of 9 of them. Let's say they're all 40. If the field of the unknown has 0 instead of null, then the Avg(Age) will incorrectly return 36: (40x9 / 10), where if the unknown is Null, Avg ignores that field in any aggregate calc it and does 40x9/9, resulting in the correct average of the known ages--40.
-Jim

 

[r937]

SQLSister, i respect your knowledge immensely, but i think your advice "In general, it is best to avoid the use of null values as much as possible" is definitely wrong

my advice: in general, allow all non-key fields to be null

jim's posts make a lot of sense

i see no reason to avoid nulls

further, with several decades of experience, i have to tell you, the only time i've encountered where allowing nulls caused a problem was in the application language (cobol/400, if you must know)

in gereal, if the programmers cannot grok nulls, the answer is not to disallow nulls, but to disallow those types of programmers!!


rudy

http://r937.com/

SQL Consulting

 

[johnherman]

see also thread183-806159

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[TJRTech]

r937, you say "my advice: in general, allow all non-key fields to be null"

What do you mean by "in general"?

Seems to me that the rule should be that NULL should be used for values that are "not required" and often "not available".

Frankly, working with UI developers, I don't want to give them the opportunity to decide which fields are and are not required. If all non-key columns are NULLable, then they can effectively pick and choose which ones they will require.

Therefore, I tend to only use NULLs for datetime columns that are close-out dates (termination, thru_date), and values for which there is no useful real-world default (blank, 0, etc), or for which the absence of setting is required.

Again, from a UI perspective, you are more likely to see UI developers pass through blank strings because the user didn't enter something, and that go into the db, than they are to convert that string to a NULL or to drop that field from an insert or a param to an SP because it was blank.

I am NOT anti-NULL, I just use them sparingly and only in the cases where there is a real need to distinquish between an assigned value and a non-assigned value. Anything else tends to be defined as a "required field" in my vocabulary.

TR

 

[r937]

what do i mean by "in general"?

you know that primary keys must be declared NOT NULL, right?

okay, so all non-PK columns can be NULL

any table, anybody's database, anytime, anywhere...

in general

okay, now for the exceptions --

if you absolutely positively gotta have a value in a column, declare it NOT NULL

otherwise, leave it NULL

that way, you can do stuff like

insert 
  into myhumungoustable
     ( myPK
     , column23
     , column52
     )
values 
     ( 937
     , 'foo'
     , 'bar'
     )

and you can be assured that the row will insert regardless of the fact that the other columns in the table have missing values

yes, you could have a whole whack of DEFAULT values, so that nothing actually ends up being NULL

but that's not the point

the point is, you don't want to have to supply default values in the INSERT statement, and you don't even want to look up the table layout to figure out whether the other col;umns have DEFAULT values or not -- all you want to do is create the row with whatever data you do have


and now, let's address your key point:

Frankly, working with UI developers, I don't want to give them the opportunity to decide which fields are and are not required. If all non-key columns are NULLable, then they can effectively pick and choose which ones they will require.

where is the database designer in this scenario?

the database designer should tell the developers which columns are NOT NULL

the developers should not get the opportunity to decide at all

but what is NULL or NOT NULL based on?

it is most certainly NOT based on an arbitrary guideline like "make all your columns not null except end dates"

that's just silly

it is based on whether the column must always have a value according to the business rules of the application

this is established in the database design phase

in general, identity is required, attributes are optional

thus, PKs are NOT NULL, non-key columns are NULL

in general





rudy

http://r937.com/

SQL Consulting

 

[TJRTech]

I obviously hit a nerve. I wasn't trying to be arbitrary. But, arbitrary to me are stances like: "Make all non-key columns nullable."

Furthermore, you may have missed my main point. I wasn't saying to make all columns non-null accept dates. My main point was that "I view" (and you can view differently) that NULLable is synonymous with "not required". And, that if you make all non-key columns nullable, then you effectively tell the developers that no field is required.

And, it has been my experience, that a preponderance of data in a database IS required. At least in the databases I design, because I steer customers away from designing systems that have superfluous data.

Again, I have nothing against use of NULL and use it often, but only for those fields that are truly not required or in which a non-assignment is required (close out dates).

TR

 

[TJRTech]

Oh, and rudy, I understand your fundamental assertion that attributes are optional.

I just don't design systems that way. I have very few optional attributes of an entity, and I have very very few default constraints as well.

And, because of this, I actually spend less time with GUI developers dealing with issues and answering questions about default values, what to do if a field is null, etc.

Oh, and I use ErWin and publish data dictionaries where each column (attribute) is described in detail.

TR

 

[r937]

only for those fields that are truly not required "

that, in general, means nulls



and yes, ain't ERwin the bomb?

i have a real love/hate relationship with that piece of cr4p

i love it for what it can do, and i hate it for being so fr1gg1in complex

i have never seen so many buttons and toolbars and wizards

oh, wait, yes i have -- homesite/cfstudio

rudy

http://r937.com/

SQL Consulting