Roger Alcindor
asked on
Duplicate Identity column
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.
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.
Peter Chan
For identity column, you must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
ASKER
The seed is 1 , the increment is 1 and has been for several years on this database
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,
ASKER
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 ?
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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.