BUG: UNIQUE Constraint on an Identity Column Can Break DRI
  
PSS ID Number: Q150452
Article last modified on 05-03-1996
 
6.00 6.50
 
WINDOWS
 

----------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft SQL Server, versions 6.0 & 6.5
----------------------------------------------------------------------
 
BUG#: 14997 (6.00)
 
SYMPTOMS
========
 
If a table involved in Declarative Referential Integrity (DRI) is created
with an identity column and UNIQUE constraint on it, DRI can fail after 88
rows of data. This table should also have a primary key column with varchar
data type.
 
create table Related(
   Related     VARCHAR(32) NOT NULL,
   ID       INTEGER IDENTITY(0,1) NOT NULL UNIQUE,
   Description VARCHAR(255),
         PRIMARY KEY (Related)
)
 
create table Parent(
   Parent      VARCHAR(32) NOT NULL,
   Related     VARCHAR(32)
      REFERENCES Related,
   Description    VARCHAR(255),
        PRIMARY KEY (Parent)
)
 
WORKAROUND
==========
 
Remove the UNIQUE constraint on the identity column.
 
STATUS
======
 
Microsoft has confirmed this to be a problem in Microsoft SQL Server
versions 6.0 and 6.5. We are researching this problem and will post new
information here in the Microsoft Knowledge Base as it becomes available.
 
MORE INFORMATION
================
 
DRI can also fail if the primary key is of type int. This happens only
after inserting 168 rows of data.
 
Additional reference words: 6.00 6.50 sql6 sql65
KBCategory: kbusage kbprg kbbug6.00 kbbug6.50
KBSubcategory: SSrvDRI
 
=============================================================================
Copyright Microsoft Corporation 1996.
