Table Column Attributes  «Prev  Next»

Lesson 4 Using the Allow Nulls flag
Objective Specify the Allow Nulls flag.

Using Allow Nulls Flag

Null values recap

A Null value indicates that a value is not known, has not been specified, or is absent. When you do not allow a null value in your column, you are specifying that a value is required in that column. By default, SQL Server assumes that values are required for all table entries. To change this default, and allow null values in your table, you have two options:
  1. Change SQL Server's default
  2. Use NULL and NOT NULL keywords

Change SQL Server's default

You can change SQL Server's default so that it automatically allows nulls in one of two ways:
  1. Check the ANSI NULL default option in the database properties dialog in the Enterprise Manager.
  2. Issue the following Transact-SQL statement in the Query Analyzer:

Exec sp_dboption database, 'ANSI NULL DEFAULT', 'True'

Substitute your database name for database above.


There will probably be times when you will want to allow or disallow null values for specific columns rather than the entire table. To specify whether specific columns will accept null values, you can use the NULL and NOT NULL keywords.
The code below specifies that a column named 'address' will allow null values.

Address varchar(30) NULL Address varchar(30)

This code specifies that the 'address' column will not allow null values.
Address varchar(30) NOT NULL

You cannot allow null values in any column that is part of the primary key. Any other column, including a foreign key, can allow null values.
In the next lesson, we will cover specifying a default value for a column.

Datatypes Length Precision Scale Nulls - Quiz

Before moving on to the next lesson, click the Quiz link below to check your knowledge of the material covered so far on datatypes, length, precision, scale, and nulls, with a short, multiple-choice quiz.
Datatypes Length Precision Scale Nulls - Quiz