Setting a already existing colum to unique using alter command.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi all!!

I want to set my emailId column in Student table to unique.

i tried

SQL

ALTER TABLE Student
 MODIFY emailId   varchar(100)     not null unique

but it is not working. what command should i use to set it to unique???

Posted 22-Nov-11 0:08am

ujjwal uniyal

Add a Solution

Comments

arindamrudra 22-Nov-11 9:18am

If you want to change the column datatype then you can use modify in Oracle. But in SQl Server Alter Table tablename Alter Columnname Datatype. You need to add a constraint for that. So, the solution of Lasly Genoese is good one and my +5 to Lasly Genoese.

3 solutions

Solution 2

Hi cheetu2810 :) ,
if it does not work it is probably because
the column already contains non-unique values ??:( .
What error message you get?

Posted 24-Nov-15 2:03am

lalasly

Comments

CHill60 25-Nov-15 5:45am

If you want to respond to a member use the "Reply" link next to their comment - otherwise they will not know that you have responded

Solution 3

you cannot set already existing columns to unique unless you are sure that the column values are all unique, even a single field in the column if is different then you can not alter the column, if so it will throw an error

if all are unique fields in that column ,than you can put an "unique" constraint to that column manually

ALTER table tablename ADD CONSTRAINT
constraintname UNIQUE NON CLUSTERED
(

columnname
)

Posted 24-Nov-15 11:12am

Member 12150801

Updated 24-Nov-15 11:17am

v3

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)

lalasly · Accepted Answer · 2011-11-22T01:05:00

Solution 1

Hi,

SQL

ALTER TABLE Student ADD  CONSTRAINT UQemail UNIQUE 
(
	emailId
)

Best regards :-)

Posted 22-Nov-11 1:05am

lalasly

Updated 22-Nov-11 1:27am

v2

Comments

ujjwal uniyal 22-Nov-11 10:08am

Thank you Lasly :)

chetna2810 4-Nov-15 11:42am

i tried the above alter query but it is not working.. :(