<div>
For identity column, you must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
</div>


<div>
The seed is 1 , the increment is 1 and has been for several years on this database
</div>


<div>
As dbaduck has indicated you are confusing the IDENTITY attribute with a UNIQUE CONSTRAINT; they are not the same. &nbsp;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,
</div>


<div>
I understand.<br />
I have tried to add a constraint by right clicking on the Constaints &quot;folder&quot; for the table in question and selecting &quot;New constraint&quot;.<br />
I am then presented with a dialogue that requires me to enter an Expression.<br />
<br />
I have tried various values for the expression such as :<br />
<br />
ALTER Table [2WSMaterials] Add Constraint CK_2WSMaterials UNIQUE(MaterialsID);<br />
or<br />
Add Constraint CK_2WSMaterials UNIQUE(MaterialsID);<br />
or<br />
UNIQUE(MaterialsID);<br />
<br />
<br />
None of the above expressions are accepted and give an error message &quot;Error validating constraint CK_2WSMaterials&quot;.<br />
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).<br />
<br />
How do I add a UNIQUE constraint to an identity column in a table ?
</div>


<div>
<div class="content wysiwyg-content">
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.<br />
Can anyone suggest how this duplication of the identity column occurred ? and whether or not, an error would have been logged somewhere ?<br />
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.
</div>
</div>


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.

Duplicate Identity column

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.