Solved

Duplicate Identity column

Posted on 2014-01-03
6
432 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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