Make a varchar(50) column unique

sql-server unique varchar
Tomas picture Tomas · Dec 9, 2011 · Viewed 27.9k times · Source

I have a column (which represents an e-mail) in a SQL Server database with varchar(50) as data type and I would like to make it unique (do not allow the same two e-mail addresses). I cannot find a way to make such column unique in SQL Server Management Studio.

How to do that?

Answer

gbn picture gbn · Dec 9, 2011

In T-SQL it would be

ALTER  TABLE  MyTable WITH CHECK 
   ADD CONSTRAINT UQ_MyTable_Email UNIQUE (EmailAddress)

Or as an explicit index

CREATE  UNIQUE INDEX IXU_Email ON MyTable (EmailAddress)

Edit: I can't see how to create a constraint in the SSMS GUI: other answers show how to manage indexes. I do only use SQL though, never the GUI for this kind of work

Related questions

How to select only the first rows for each unique value of a column

Let's say I have a table of customer addresses: CName | AddressLine ------------------------------- John Smith | 123 Nowheresville Jane Doe | 456 Evergreen Terrace John Smith | 999 Somewhereelse Joe Bloggs | 1 Second Ave In the table, one customer like John Smith can have multiple addresses. I need …

Read More
SQL - select distinct only on one column

I have searched far and wide for an answer to this problem. I'm using a Microsoft SQL Server, suppose I have a table that looks like this: +--------+---------+-------------+-------------+ | ID | NUMBER | COUNTRY | LANG | +--------+---------+-------------+-------------+ | 1 | 3968 | UK | …

Read More
How to create a unique index on a NULL column?

I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS. My current solution involves a unique index on a view like so: CREATE VIEW vw_unq WITH SCHEMABINDING AS SELECT …

Read More