BUG: Primary Key Can Incorrectly Include a Nullable Column (180189)

SYMPTOMS

BUG #: Windows NT: 17741 (6.5)

If the "Columns Null by Default" option is selected for a database, the following CREATE TABLE statement will create a table with a nullable column included in the primary key:

   create table test(column1 varchar(12)
    CONSTRAINT mykey PRIMARY KEY (column1, column3),
   column2 varchar(2),
   column3 varchar(2))

This statement should not allow the primary key to have a nullable column. Either the primary key should be created with all columns defined as not null, or the following error message should be returned:

Msg 8111, Level 16, State 0 Attempting to define PRIMARY KEY constraint on nullable column in table <table name>.

WORKAROUND

To work around this behavior, do either of the following:

Make sure the "Columns Null by Default" option is not selected. -or-
Define the primary key after all of the columns have been defined.

For example, the following CREATE TABLE statement will create a primary key without nullable columns regardless of whether or not the "Columns Null by Default" option is selected:

   create table test(column1 varchar(12),
   column2 varchar(2),
   column3 varchar(2),
   CONSTRAINT mykey PRIMARY KEY (column1, column3))

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.