Solved

Duplicate Identity column

Posted on 2014-01-03
6
423 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
6 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
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
Comment Utility
The seed is 1 , the increment is 1 and has been for several years on this database
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 350 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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 150 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now