Getting Error while adding CONSTRAINT to the table using alter table command

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 4-Nov-15 19:59pm

chetna2810

Updated 4-Nov-15 22:45pm

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Accepted Answer · 2015-11-04T23:03:00

Solution 1

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.

Posted 4-Nov-15 23:03pm

phil.o

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