Using Allow Nulls Flag

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

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'

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)

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.

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