Working with Defaults and Null Values

If you specify NOT NULL when you create a column but do not create a default for the column, SQL Server returns an error message whenever anyone inserts a row and fails to make an explicit entry in that column.

When you use the INSERT statement to specify default values for columns, you get the following results:

If a DEFAULT constraint or default for a column does not exist, but the column allows null values, NULL will be inserted.
If any column of the table does not have a DEFAULT constraint or default and does not allow NULL, an error will be returned.

When you drop a default for a NULL column, SQL Server inserts NULL in that position whenever you add rows without entering a value for that column. When you drop a default for a NOT NULL column, you get an error message when rows are added but no value for that column is entered.

The following table illustrates the relationship between the existence of a DEFAULT constraint or default and the definition of a column as NULL or NOT NULL. The entries in the table show the result:



Column definition	No entry, no default	No entry, default	Enter NULL, no default	Enter NULL, default

NULL	NULL	Default	NULL	NULL
NOT NULL	Error	Default	Error	Error

For more information about null values, see Null Valuesin the Microsoft SQL Server Transact-SQL Reference.