BUG: Alter Table May Fail to Create Primary Key Constraint

ID: Q136110

The information in this article applies to:
BUG# NT: 11208 (6.00)

SYMPTOMS

If an ALTER TABLE statement that adds a primary key constraint fails with an error 1750:

Unable to create constraint. See previous errors.

then any subsequent attempts to add a clustered primary key to the table will also fail. The errors you may see will vary depending on the type of primary key that will be subsequently created.

When the next attempt to create a primary key is to be a clustered index, the create index command will fail with error 1902:
Cannot create more than one clustered index on table '...'.
Drop existing clustered index '1' before creating another.

If you examine the system tables, there is no clustered index on the table nor are there any constraints listed for the object.



An attempt to create a nonclustered index as a primary key may fail with the error 602:
Could not find row in Sysindexes for dbid '5', object '...', index '..'
Run DBCC CHECKTABLE on Sysindexes. The SQL Server is terminating this
process.


CAUSE

SQL Server maintains a structure that is used to manage objects accessed by processes within the server. For additional information, please see the following article in the Microsoft Knowledge Base:

Q114843 : INF: Explanation of Open Objects

Within the open object structure, a bit is set to indicate that a table object has a clustered index. In this scenario, the bit is not being cleared when the initial command to create the primary key fails.


WORKAROUND

You can correct the problem by stopping and restarting SQL Server, which will re-initialize the open object structure for the table. Otherwise, you can drop and recreate the table.


STATUS

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

Additional query words: sql6 ref int 


Keywords          : kbprg SSrvProg SSrvTran_SQL kbbug6.00 
Version           : 6.0 6.0.121
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 19, 1999