Why is my Unique Index Failing with 'Invalid Key Column Type'?

Addressing Database Error: Invalid Key Column Type for Index

When creating a unique index on a table, you may encounter the error "Column '[key]' in table '[table]' is of a type that is invalid for use as a key column in an index." This error typically occurs when the data type of the column specified for the index is too large.

Resolving the Issue

The typical cause of this error is an index key with a maximum length that exceeds the permissible limit. In Microsoft SQL Server, the maximum length for an index key is 900 bytes.

To resolve this issue, consider reducing the maximum length of the key column. In your case, the key column is defined as nvarchar(max), which allows for unlimited length. For index keys, it's advisable to use a limited data type such as nvarchar(450), which ensures that the index key's length is within the допустимые пределы.

Here's an updated table definition with the key column limited to 450 characters:

CREATE TABLE [misc_info] (
    [id] INTEGER PRIMARY KEY IDENTITY NOT NULL, 
    [key] NVARCHAR(450) UNIQUE NOT NULL, 
    [value] NVARCHAR(MAX) NOT NULL
);

By making this adjustment, you ensure that the key column meets the size requirements for an index key, resolving the error.

The above is the detailed content of Why is my Unique Index Failing with 'Invalid Key Column Type'?. For more information, please follow other related articles on the PHP Chinese website!