March 31, 2014 by Kenneth Fisher
Ever assume that when you don’t specify NULL or NOT NULL on a new column it’s going to allow NULLs? I always did. Turns out I was wrong.
When you don’t specify NULL vs NOT NULL it will usually default to NULL. Here are some exceptions where it will default to NOT NULL.
- Use the PRIMARY KEY clause on column creation
CREATE TABLE Null_Test (NotNullCol varchar(50) PRIMARY KEY );
- Data type is timestamp
CREATE TABLE Null_Test (NotNullCol timestamp );
- Data type is an user data type that was defined as NOT NULL
CREATE TYPE NotNull FROM varchar(50) NOT NULL; GO CREATE TABLE Null_Test (NotNullCol NotNull ); GO
- SET ANSI_NULL_DFLT_OFF is set ON
SET ANSI_NULL_DFLT_OFF ON; GO CREATE TABLE Null_Test (NotNullCol varchar(50) ); GO
- The ANSI_NULL_DEFAULT setting of the database is set to OFF and both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON are set to OFF
To be safe it’s probably a good idea to include NULL or NOT NULL when defining a column. Just saying.