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;
    CREATE TABLE Null_Test (NotNullCol NotNull );
    CREATE TABLE Null_Test (NotNullCol varchar(50) );
  • 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: Logo

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 3,753 other subscribers

Follow me on Twitter

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