Microsoft KB Archive/150452

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

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.