Default nullability of a new column

Leave a comment

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: