• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Duplicate Identity column

I have experienced an issue where a table that has an identity column had two records with the same value in the identity column. The table was an article in a merge publication.
Can anyone suggest how this duplication of the identity column occurred ? and whether or not, an error would have been logged somewhere ?
A third party has since made changes and the data is no longer in the same state so I am now unable to do any meaningful checks on the data.
0
alcindor
Asked:
alcindor
2 Solutions
 
HuaMinChenBusiness AnalystCommented:
For identity column, you must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
0
 
alcindorAuthor Commented:
The seed is 1 , the increment is 1 and has been for several years on this database
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, an Identity column just indicates that it will get its value from the IDENTITY value for that table and column.  It will never guarantee that it will be unique or avoid collisions, that is your job.

I could set an IDENTITY column at 1,1 and then let it get to 100,000 and then reset it to 1 and it will give me 1 and continue.  

Now if I have a UNIQUE constraint on that column, or a PRIMARY KEY then yes duplicates could be a problem.  Because you have it in a merge replication article, then if the data set on the other side has IDENTITY values that conflict, they will get put in the database with their identity values, not values from the current tables identity.

This is how it could happen.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
As dbaduck has indicated you are confusing the IDENTITY attribute with a UNIQUE CONSTRAINT; they are not the same.  A column with an IDENTITY attribute can have repeated values (You could write a SQL script with a few lines to duplicate this). It may not be desirable, but there is nothing technically wrong with it,
0
 
alcindorAuthor Commented:
I understand.
I have tried to add a constraint by right clicking on the Constaints "folder" for the table in question and selecting "New constraint".
I am then presented with a dialogue that requires me to enter an Expression.

I have tried various values for the expression such as :

ALTER Table [2WSMaterials] Add Constraint CK_2WSMaterials UNIQUE(MaterialsID);
or
Add Constraint CK_2WSMaterials UNIQUE(MaterialsID);
or
UNIQUE(MaterialsID);


None of the above expressions are accepted and give an error message "Error validating constraint CK_2WSMaterials".
I have checked the data in the table and I can guarantee that there are no duplicates (there are only 3 records in the table).

How do I add a UNIQUE constraint to an identity column in a table ?
0
 
Anthony PerkinsCommented:
As dbaduck has explained you could make the IDENTITY column a PRIMARY KEY or quite simply add a UNIQUE index on that column.

However since this is in a MERGE Replication you need to be careful as this would mean that the data would not be replicated.

What you really need to do is address the real problem as to why it is happening in the first place.  I suspect the problem is that the IDENTITY columns were not setup correctly to support replication.  For example, one strategy to prevent the values being duplicated is if you just have two servers involved in replication, then you can set up one server to use the even numbers and the other odd numbers.  Another approach that I believe is more scalable is to manually set ranges.  So in this last approach, one server would have the range of say 1-999,999, the next one 1,000,000-1,999,999, etc.

Try searching for articles on Merge Replication and IDENTITY columns and select the best approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now