Allow NULL or Define DEFAULT Value

[binder]

I am designing a new table with a few columns that may or may not have
a value on each row that is inserted.

What issues determine whether to allow a NULL value to be inserted for
that column or define a default value to be used?

I want to think through the repercussions of this decision before I get
into production.

[binder]

Re: Allow NULL or Define DEFAULT Value


binder wrote:

I am designing a new table with a few columns that may or may not have
a value on each row that is inserted.
>
What issues determine whether to allow a NULL value to be inserted for
that column or define a default value to be used?
>
I want to think through the repercussions of this decision before I get
into production.

>From a programmatic standpoint, if I have a column that may or may not

have a value, is it better to insert a default value that indicates no
value was entered, such as 0 for a userid, or insert a NULL value?

[Erland Sommarskog]

Re: Allow NULL or Define DEFAULT Value

binder (rgondzur@gmail .com) writes:

binder wrote:

>I am designing a new table with a few columns that may or may not have
>a value on each row that is inserted.
>>
>What issues determine whether to allow a NULL value to be inserted for
>that column or define a default value to be used?
>>
>I want to think through the repercussions of this decision before I get
>into production.

>
From a programmatic standpoint, if I have a column that may or may not
have a value, is it better to insert a default value that indicates no
value was entered, such as 0 for a userid, or insert a NULL value?

Programmatic? That's the wrong standpoint to look at it. You should look
at what it means.

Say that you have a column called whotoblameusrid , and no explicit value
is inserted. If you let it be NULL, means that in this case there is
no one to blame. (After all, anyone who is acquainted with Elvis Costello's
early material knows that Accidents can Happen.) If you use a default
value of 0 and 0 is Cain's user id, this mean that we Blame it on
Cain when no one else is at fault. (Costello fans know what I'm talking
about.)

But must 0 be a certain user? Yes, because good database design says
that a userid should be a foreign key to a table that defines users,
so there must be a user with id 0.

This also applies to non-key columns. Say a column that represents
an amount, for instance the cost for something. NULL would indicate
that the price is unknown (and we probably should not sell it). 0
means that the goods is for free.

That is not to say that default values should not be used. For instance
if you open a new account, it makes perfect sense to have default of
0 for the holdingsamt column, because you start with 0 and you may
not make a deposit immediately.

Simply, having NULL or a default value depends on what not entering a
value means. And by the way, a column could permit NULLs, but still have
a default value, because it's only exceptional that the value is not
known. For instance, a column "citizenof" could very well have the
default value of SE for a Swedish system, but the column must permit
NULL to account for stateless persons.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[David Portas]

Re: Allow NULL or Define DEFAULT Value

Erland Sommarskog wrote:

>
Programmatic? That's the wrong standpoint to look at it. You should look
at what it means.
>
Say that you have a column called whotoblameusrid , and no explicit value
is inserted. If you let it be NULL, means that in this case there is
no one to blame. (After all, anyone who is acquainted with Elvis Costello's
early material knows that Accidents can Happen.) If you use a default
value of 0 and 0 is Cain's user id, this mean that we Blame it on
Cain when no one else is at fault. (Costello fans know what I'm talking
about.)
>
But must 0 be a certain user? Yes, because good database design says
that a userid should be a foreign key to a table that defines users,
so there must be a user with id 0.
>
This also applies to non-key columns. Say a column that represents
an amount, for instance the cost for something. NULL would indicate
that the price is unknown (and we probably should not sell it). 0
means that the goods is for free.
>
That is not to say that default values should not be used. For instance
if you open a new account, it makes perfect sense to have default of
0 for the holdingsamt column, because you start with 0 and you may
not make a deposit immediately.
>
Simply, having NULL or a default value depends on what not entering a
value means. And by the way, a column could permit NULLs, but still have
a default value, because it's only exceptional that the value is not
known. For instance, a column "citizenof" could very well have the
default value of SE for a Swedish system, but the column must permit
NULL to account for stateless persons.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Don't forget the third option: decompose the optional attribute(s) into
another table. To extend Erland's example, the WhoToBlameUsrID can go
in a table along with any other columns that relate only to Blame.

CREATE TABLE Who (WhoID INT NOT NULL PRIMARY KEY /* ... The required
attributes for the Who table ... */);

CREATE TABLE Blame (WhoID INT NOT NULL PRIMARY KEY REFERENCES Who
(WhoID), WhoToBlameUsrID INT NOT NULL /* ... The optional "Blame"
attributes ... */);

The principle at work here is that an entity is determined by its
unique set of attributes. If you analyse the functional dependencies
you find you have more entities than you currently have tables for -
that's what tells you to decompose.

In SQL Server this approach has one special advantage. SQL Server's
UNIQUE constraint treats nulls like values. The constraint doesn't
permit nulls to be duplicated, which means that unique constraints are
of limited use for optional attributes. So if an optional column may
need to be part of a unique constraint you should certainly consider
the decomposition approach.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--