Solved

Duplicate Identity column

Posted on 2014-01-03
6
425 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
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 24

Accepted Solution

by:
DBAduck - Ben Miller earned 350 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 150 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

867 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

25 Experts available now in Live!

Get 1:1 Help Now