Is of a Type That Is Invalid for Use as a Key Column in an Index

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

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).

Entity Framework Core: Column 'Id' in table 'Users' is of a type that is invalid for use as a key column in an index

It needs to remove all migrations when changing a database service.

DbGeography is of a type that is invalid for use as a key column in an index or statistics

Unfortunately, Spatial Types cannot be used as entity keys in Entity Framework since they work like Filtered Indexes. This post contains more information about using Spatial Types in the Entity Framework.

Error while creating unique clustered index in view in SQL Server

Unique Constraint can hold up to 8000 bytes per row. So if the maximum length of the column allows to store more than 8000 bytes, you will get error.

CREATE TABLE dbo.Temp
(
    Name VARCHAR(901) UNIQUE
)

This code gave me the following warning

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

While this works fine

create TABLE dbo.Temp
(
    Name VARCHAR(900) UNIQUE
)

If You Use VARCHAR(MAX) or NVARCHAR(MAX) You Will get this Exact error

create TABLE dbo.Temp
(
    Name VARCHAR(max) UNIQUE
)

So make sure that the maximum allowable size of the column Name is less than 900 bytes

Creating string index with Code first

Usually you get this error when you use a VARCHAR(Max) try using:

[Column(TypeName = "VARCHAR")]
[StringLength(n)]
[Index]
public string CreatedBy { set; get; }

where n is between 1 and 450.

---

Related Topics