BUG: Primary Key Can Incorrectly Include a Nullable Column

ID: Q180189

The information in this article applies to:

Microsoft SQL Server, version 6.5

BUG #: Windows NT: 17741 (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))

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.

Additional query words: ANSI_NULL_DFLT_ON

Keywords          : SSrvTran_SQL kbbug6.50 
Version           : WINNT:6.5
Platform          : winnt
Issue type        : kbbug

Last Reviewed: February 1, 1998