SQL Server - Primary Keys

I will say that it's amazing what you find out when you try things a different way. Presumably we all know that columns taking part in a primary key cannot be NULL but have you ever tried the different ways of creating them and seeing what happens? Take the following for instance:

CREATE TABLE [#tab1]
(
        [my_key_col1] INT PRIMARY KEY CLUSTERED,
        [multiplier]  FLOAT
);

Note how the default for a column is to be nullable when not specified yet the statement succeeds. Looking at the table definition (i.e. change into [tempdb], highlight the name of the table and press ALT-F1) you can see that the column taking part in the primary key has actually been set to NOT NULL. So how about this way:

CREATE TABLE [#tab2]
(
        [my_key_col1] INT,
        [multiplier]  FLOAT,
                PRIMARY KEY CLUSTERED([my_key_col1])
);

This works as well, also setting the column taking part in the primary key to NOT NULL. Clearly SQL Server will set any columns taking part in a primary key to be not nullable when "nullability" is not specified in the column definition. i.e. Leaving it to use the defaults. If you do specifically set the column to be NOT NULL then the create statement will fail. Indeed, books online for SQL Server 2008 for the CREATE TABLE construct states:

All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL.
If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

Now what about the following?

CREATE TABLE [#tab3]
(
        [my_key_col1] INT,
        [multiplier]  FLOAT
);
ALTER TABLE [#tab3] ADD PRIMARY KEY CLUSTERED([my_key_col1]);

As expected this fails to add the primary key because the "nullability" of the column has been defined at the time the primary key is to be added. Once the table has been materialised all aspects of its definition have been put in place. Thus trying to add a primary key to columns defined as nullable must fail.

I always explicitly specify the "nullability" of columns in the definition of all tables I create, including temporary tables, which is why the first two examples surprised me. I was not expecting SQL Server to override the defaults. Hmmmm, just goes to show that we are never too old to learn eh?

Now before we finish, let's clean up after ourselves...

DROP TABLE [#tab1], [#tab2], [#tab3];