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

entity-framework, sql, sql-server, unique

Solution

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.

Problem

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?

Original source