NULL Values, Blank Values, and Table Design

Have you dealt with NULL yet? A field is referred to as NULL if it has not received a value.

To specify the NULL-ability of a column using T-SQL, just add NULL after the datatype for the column. To specify that values are required, add NOT NULL. If you don’t specify NULL or NOT NULL, the column will default to NULL.

Here is the code to explain this a bit further

CREATE TABLE Person
(
      personID INT IDENTITY(1,1)
    , firstName VARCHAR(20) NULL
    , lastName VARCHAR(20) NOT NULL
)

In this example, first name can be NULL, but lastName cannot.

If the table already exists and it holds some data already, you cannot set the NOT NULL option for columns that don’t have values. You’ll have to run an UPDATE statement and fill in a default value before you can set the NOT NULL option.

It may also be worth considering a DEFAULT value for columns you wish to prevent NULL values in. That will allow the interpreter to fill in a value when the user forgets to add it.

But this may not be your intention. You may actually need a user selected value. In that case, setting this option before the users have a chance to mess things up will save you a lot of heartache!

If you wish to alter a table, and change the NULL setting for a column, please use the following statements.

ALTER TABLE Person ALTER COLUMN firstName NULL
GO
ALTER TABLE Person ALTER COLUMN lastName NOT NULL

If you have any questions, please send them in!

Tags: , ,

No comments yet.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.