BUG: Primary Key Can Incorrectly Include a Nullable Column

ID: Q180189

The information in this article applies to:

Microsoft SQL Server version 6.5

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.

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