Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Duplicate Identity column

Posted on 2014-01-03
6
Medium Priority
?
457 Views
Last Modified: 2014-01-05
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
Comment
Question by:alcindor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39753340
For identity column, you must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
0
 
LVL 2

Author Comment

by:alcindor
ID: 39753358
The seed is 1 , the increment is 1 and has been for several years on this database
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1400 total points
ID: 39754977
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39756738
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
 
LVL 2

Author Comment

by:alcindor
ID: 39757194
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 39757486
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question