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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *