Microsoft KB Archive/322818

= BUG: Incorrect Identity Value with Self-Referencing FOREIGN KEY Constraint =

Article ID: 322818

Article Last Modified on 7/17/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q322818



BUG #: 55275 (SQLBUG_70)



SYMPTOMS
If you have a PRIMARY KEY constraint that is defined on an identity column, and you also have a FOREIGN KEY constraint that is defined on the identity column that references itself, SQL Server generates an incorrect identity value. The generated identity value is two times the incremental value that is defined in the identity column property instead of one times the incremental value.

Additionally, the @@IDENTITY system function returns an incorrect value. Instead of returning the last-inserted value, it returns the last-inserted value, and the incremental seed value.



WORKAROUND
To work around this problem, remove the self-referencing FOREIGN KEY constraint on the column.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Problem
CREATE TABLE [dbo].[tb_dataPDID]

(PDID INT IDENTITY (1000, 1) NOT NULL    CONSTRAINT PK_PDID PRIMARY KEY, PapaPDID INT NULL    CONSTRAINT FKPDID FOREIGN KEY (PDID) REFERENCES tb_dataPDID(PDID), Alias  VARCHAR(255) NULL) GO

insert tb_dataPDID (Alias) values (1) insert tb_dataPDID (Alias) values (2) insert tb_dataPDID (Alias) values (3) insert tb_dataPDID (Alias) values (4) GO

SELECT * FROM tb_dataPDID GO

SELECT @@Identity GO

The identity column is defined as IDENTITY(1000,1); however, it is incorrectly incremented by two instead of by one.

Additionally, @@IDENTITY incorrectly returns 7 instead of 6 (the last-inserted value).

Keywords: kbbug kbpending KB322818

-

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

© Microsoft Corporation. All rights reserved.