I had watch youtube video tutorial teaching how to create unique key
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?
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
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
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)
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.