Identity Question: why is value still used when there is an INSERT error

[Angel]

Hi

I'm just wondering why the IDENTITY value is still used when you attempt
an INSERT that fails?

I placed a Foreign Key Constraint on my table. When I put bogus data in
the foreign key field to break the INSERT and get a 'Referential integrity Violated, Unable to Insert error' the Identities are still used!

For example, the Identity was last at 5. Then, I try 10 "bad" Inserts with Foreign Key Violations. The next time I Insert a record Successfully, the indentity starts at 16!!

why does the Identity get incremented when the Insert fails?

Angel

[Ray Miao]

Run 'dbcc checkident'.


------------
Angel at 10/18/99 3:20:34 PM

Hi

I'm just wondering why the IDENTITY value is still used when you attempt
an INSERT that fails?

I placed a Foreign Key Constraint on my table. When I put bogus data in
the foreign key field to break the INSERT and get a 'Referential integrity Violated, Unable to Insert error' the Identities are still used!

For example, the Identity was last at 5. Then, I try 10 "bad" Inserts with Foreign Key Violations. The next time I Insert a record Successfully, the indentity starts at 16!!

why does the Identity get incremented when the Insert fails?

Angel