Allowing Duplicate ID

Question

User1122355199 posted

Hello everyone and thanks for your help in advance.  I am debugging an application that inserts records into a SQL table.  Due to a bug, the application caused the record to insert multiple times.  Since the app is still in development, not a big deal, however, I ran into another problem I did not anticipate nor do I understand how it happened.  The table has an ID column, and the column Identity Specification is set to yes as well as the Is Identity.  Identity Increment and Identity Seed are boht set to 1.  However when the multiple insert occurred, the ID for each row is the same number.    I don't know why or how this happened.  Strangely, on non-duplicate records that were inserted, the ID column incremented correctly.  So I have no idea how or why records with the same ID could be inserted into this table.  Any help would be appreciated.

Answer 1

User753101303 posted

Hi,

This is SQL Server? AFAIK you can't provide a value for an identity column unless using SET IDENTITY_INSERT: https://msdn.microsoft.com/en-us/library/ms188059.aspx . Do you remember to use this somewhere?

Anyway if this is an ID column it would be good idea to make it a PRIMARY KEY: http://www.w3schools.com/sql/sql_primarykey.asp

Even if you don't find right now the issue, the huge benefit is that you'll then have an error as soon as it happens and so it should allow to narrow down where is the problem (of course you'll have first to remove those duplicates).

If still not found we would need to see some code to understand how you are doing those multiple inserts. It is hard to do that on identity columns and it will be impossible if you add a primary key.

Answer 2

User1122355199 posted

Thanks for the response.  Does it matter whether the ID is set to data type Int or BigInt?  After further inspection, there are two other columns that have a default date of GetDate() and the datetime is identical.  I'm really not understanding how this can happen.

Answer 3

User364663285 posted

Hello everyone and thanks for your help in advance.  I am debugging an application that inserts records into a SQL table.  Due to a bug, the application caused the record to insert multiple times.  Since the app is still in development, not a big deal, however, I ran into another problem I did not anticipate nor do I understand how it happened.  The table has an ID column, and the column Identity Specification is set to yes as well as the Is Identity.  Identity Increment and Identity Seed are boht set to 1.  However when the multiple insert occurred, the ID for each row is the same number.    I don't know why or how this happened.  Strangely, on non-duplicate records that were inserted, the ID column incremented correctly.  So I have no idea how or why records with the same ID could be inserted into this table.  Any help would be appreciated.

Before doing insert, you need a pre-check to ensure that the same PK or Unique key would not be violated upon the creation of the new record!

Answer 4

User1122355199 posted

How?

Answer 5

User-158764254 posted

How?

If you are asking how to pre-check for a duplicate, i wouldn't go down that path myself.

I'd do what PatriceSc suggested and enforce uniqueness at the database itself.

http://www.w3schools.com/sql/sql_unique.asp

a unique constrain should result in an error when a duplicate is being created and that way you can track down how this issue is happening.

Answer 6

User753101303 posted
To be on the safe side you see that by selecting from this table only? (or could it be a wrong join causing you to think you have duplicates?)

Can you confirm this is SQL Server? For now the weird thing is that unless this is explicitely enabled, identity values arr created for you and you can't even provide a value for this column when doing an insert.

Answer 7

User1122355199 posted

Thanks for the response.  Yes, it is SQL Server which is why I am baffled.  I don't have any checking logic because I didn't think it was needed.

Answer 8

User753101303 posted

It is not needed. For identity columns new values are generated by SQL Server and you can't even provide a value when doing an insert (unless explicitely enabled before):

CREATE TABLE Test(Id BIGINT IDENTITY NOT NULL,Data VARCHAR(10))
GO
 -- OK
INSERT INTO Test(Data) VALUES ('A')

 -- FAILS: you can't insert an explicit value unless using IDENTITY_INSERT
INSERT INTO Test(Id,Data) VALUES (10,'B')

So the behavior you described should just not happen. This is why I suspect:
- either a wrong query causing to mistakenly think you have duplicates
- or your column is not an identity column
- or you have some code that uses IDENTITY_INSERT
- or maybe if you reseed the identity value to a wrong value it could happen

Also the IDENTITY attribute is just about generating new values. As suggested earlier I would add a PRIMARY KEY constraint which will ensure you can't have duplicate values for this identity column. It might help to discover where it happens as then it will fail as soon as you'll try to insert a duplicate value.

Or to start with what if you test in SQL Server Management Studio an INSERT query that insert a row and provides an explicit value for the identity column? Does it work?

Answer 9

User1122355199 posted

Thanks for the response.  After reading your post, I searched legacy code and found a webservice that inexplicably used IDENTITY_INSERT.  This appears to have been the problem.  Thanks for the help.