BUG: Primary Key Can Incorrectly Include a Nullable Column

• Microsoft SQL Server, version 6.5

SYMPTOMS

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))

   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.

   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.