How to create Unique key for a varchar column for entity framework database?

Question

I had watch youtube video tutorial teaching how to create unique key

http://www.youtube.com/watch?v=oqrsfatxTYE&list=PL08903FB7ACA1C2FB&index=9

In the video, he has created a unique key for Email(nvarchar) column, I could create it when I create database manually, but when I try create unique key for a database created with entity framework code first, using the next query

ALTER  TABLE Peoples
   ADD CONSTRAINT UQ_MyTable_Email UNIQUE (email)

It will generate a error:

Msg 1919, Level 16, State 1, Line 2
Column 'email' in table 'Peoples' is of a type that is invalid for use as a key column in an index.

What is problem? what can I do for create unique key for nvarchar(max) column?

Answer 1

say If you create this table

CREATE TABLE ConstTable 
(ID INT, 
Email VARCHAR(1000)
CONSTRAINT uc_Email UNIQUE (Email)
)
GO

you will get a warning :

Warning! The maximum key length is 900 bytes. The index 'uc_Email' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail

Your column on which you want to define a unique constraint should be less then or equal to 900 bytes, so you can have a VARCHAR(900) or NVARCHAR(450) column if you want to be able to create a unique constraint on that column

Same table above with VARCHAR(450) gets created without any warning

CREATE TABLE ConstTable 
(ID INT, 
Email VARCHAR(900)
CONSTRAINT uc_Email UNIQUE (Email)
)
GO

Result

Command(s) completed successfully.

Test For your Table

say this is your table

CREATE TABLE ConstTable 
(ID INT, 
Email VARCHAR(MAX)
)
GO

Now try to create any index on the VARCHAR(MAX) data type and you will get the same error.

CREATE INDEX ix_SomeIdex
ON ConstTable (Email)

Error Message

Msg 1919, Level 16, State 1, Line 1 Column 'Email' in table 'ConstTable' is of a type that is invalid for use as a key column in an index.