BUG: Incorrect Identity Value with Self-Referencing FOREIGN KEY Constraint (322818)


The information in this article applies to:

  • Microsoft SQL Server 7.0
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.

MORE INFORMATION

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).
Modification Type:MajorLast Reviewed:7/17/2002
Keywords:kbbug KB322818
©2002 Microsoft Corporation. All rights reserved.