Click here to Skip to main content
15,903,030 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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.

Hi,


SQL
ALTER TABLE Student ADD  CONSTRAINT UQemail UNIQUE 
(
	emailId
)



Best regards :-)
 
Share this answer
 
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.. :(
Hi cheetu2810 :) ,
if it does not work it is probably because
the column already contains non-unique values ??:( .
What error message you get?
 
Share this answer
 
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
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
)
 
Share this answer
 
v3

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