Microsoft KB Archive/180189

= BUG: Primary Key Can Incorrectly Include a Nullable Column =

Article ID: 180189

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q180189



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.



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: kbbug KB180189

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.