CODEWITHSUNDEEP
sqlsql-serversql-server-2008
hitesh.gawhade
hitesh.gawhade

Reputation: 347

Column in table is of a type that is invalid for use as a key column in an index

I have a table where I am storing employee details. I want to alter the table and set one of the column emp_code as primary key. Its datatype is nvarchar(max), but I am not able to set it as primary key.

I run the following query : 

ALTER TABLE user_master
ADD PRIMARY KEY (emp_code)

but it gives me an error :

Msg 1919, Level 16, State 1, Line 1
Column emp_code in table user_master is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

How can I overcome this?

Upvotes: 17

Views: 32917

Answers (3)

dheeraj
dheeraj

Reputation: 11

Yes you are correct.Warning! The maximum key length is 900 bytes. The index PK__bookmark__2DFA2B0E164452B1 has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail. not use of varchar(max)

Upvotes: 1

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

You can't use varchar(max). It isn't designed to store primary keys.

Find out what the longest employee code, and create a sized nvarchar that covers your longest case.

e.g. longest employee code is 70 characters, try:-

nvarchar(100)

...just to cover yourself.

Upvotes: 6

ta.speot.is
ta.speot.is

Reputation: 27214

An index's key cannot exceed a total size of 900 bytes. Change the data type to NVARCHAR(450). If that is not suitable, use a surrogate key (typically an IDENTITY column).

Upvotes: 32

Related Questions