Click here to Skip to main content
15,905,563 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Getting Error " The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.StudentAdd' and the index name 'ukStIdAdd'. The duplicate key value is (<null>)." while adding CONSTRAINT to the table using alter table command

SQL
Where I tried this command but not working

"ALTER TABLE StudentAdd ADD CONSTRAINT ukStIdd UNIQUE (StIdAdd)"

where StudentAdd is Table Name. ukStIdd Constraint name and StIdd Column Name
Posted
Updated 4-Nov-15 22:45pm
v3
Comments
Tomas Takac 5-Nov-15 3:04am    
Good for you. So what's the problem?
phil.o 5-Nov-15 3:50am    
Not a question. What prevents you from reading the documentation?
chetna2810 5-Nov-15 4:11am    
I tried this command but not working

"ALTER TABLE StudentAdd ADD CONSTRAINT ukStIdd UNIQUE (StIdAdd)"

where StudentAdd is Table Name. ukStIdd Constraint name and StIdd Column Name.
Tomas Takac 5-Nov-15 4:17am    
You should update your question with additional data not post it in comments: please click "Improve question" button. Also specify what does it mean "it's not working": if you get an error message then include the error message in your question. Last but not least, you need to "reply" (click the button which appears next to the poster's name) to a comment otherwise people don't get notified of your comment.
phil.o 5-Nov-15 4:23am    
'Not working' is not descriptive at all. Please indicate the exact error message you are getting.

1 solution

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.StudentAdd' and the index name 'ukStIdAdd'. The duplicate key value is ().

This means that, at the time you are trying to set up the constraint, there already are some duplicate values in the column. Thus, it will be impossible to set up the constraint.

You have several choices:
- whether you sanitize your table before setting up the constraint (I mean, you take care that there are no duplicates in the column on which you want to put the constraint). Depending on the size of the table it could be time-costly.
- or you give up the idea to put this constraint on this column.

Good luck.
 
Share this answer
 
Comments
chetna2810 5-Nov-15 6:25am    
there is no record in the column. Table is having more than 2k records but that column is empty. Even i have added that column and dont want duplicate record thats why want to add constraint to it.
phil.o 5-Nov-15 6:33am    
So, if this column is empty, there is the same value (NULL) is each row. Which violates the unique constraint.
You need to qualify it with unique values before setting up the constraint.
chetna2810 5-Nov-15 12:37pm    
My problems is solved but still is there any other way to add the unique constraint because if u have to add one new column with unique constraint and table having a record around 2k.
thanks in advance.
phil.o 5-Nov-15 13:32pm    
No there isn't. The problem is not the way you add the constraint, the problem is that, from start, the constraint is unapplicable because the column already holds duplicate values.
Usually constraints are added at table creation, when there is no data than can violate the constraint. That's why it is recommended to think about a database's architecture long before beginning to feed it with data.
chetna2810 5-Nov-15 6:44am    
Got it !!
Thanks You..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900