-1

When a column in a table is PRIMARY KEY as well as IDENTITY KEY and another column in the same table acts as Foreign key. Then on Inserting any value in the Foreign key column, that does not exists in the Reference table column, the editor throws error due to Foreign Key Constraint which is understandable. But inspite of that value not getting inserted in the table, the IDENTITY COLUMN GETS INCREMENTED resulting in an behavior where, on Inserting valid values the IDENTITY COLUMN skips one increment.

For Ex.

Lets take a REFERENCE TABLE tbl__Gender

enter image description here

And a tbl__Customer TABLE :

enter image description here

CREATE A FOREIGN KEY REFERENCE:

ALTER TABLE tbl__Customer ADD CONSTRAINT FK_tbl__Customer_Gender_ID FOREIGN KEY (Gender_ID) REFERENCES tbl__Gender(ID)

TRY TO INSERT ANY INVALID VALUE IN GENDER_ID:

INSERT INTO tbl__Customer VALUES ('ana', 2)

INSERT INTO tbl__Customer VALUES ('erik',200)

ERROR IS DISPLAYED: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbl__Customer_Gender_ID".

INSERTING A VALID VALUE:

INSERT INTO tbl__Customer VALUES ('ana', 1)

AFTER EXECUTION THE OUTCOME IS(WITH ID = 4):

enter image description here

WHY IS IT SO? WHY DOES IT MISS OUT 2 AND 3 IN ID COLUMN?

sam05
  • 199
  • 2
  • 4

1 Answers1

2

This is normal behaviour.

As outlined here:

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

http://msdn.microsoft.com/en-GB/library/ms187342.aspx

Darren
  • 68,902
  • 24
  • 138
  • 144