Why Does SQLite Throw an 'Invalid Key Column Type for Index' Error, and How Can I Fix It?

Error: Invalid Key Column Type for Index

SQLite encounters an error when indexing columns with certain data types. One such error occurs when attempting to use an nvarchar(max) column as a key column in an index, resulting in the error message:

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

Understanding the Limitation

SQLite imposes a maximum size limit on index keys, restricting their length to 450 characters. This limitation arises from the 8000-byte per row constraint on unique constraints, which includes both the key and any additional indexed columns.

Solution: Modifying Key Column Data Type

To resolve this issue, you can modify the data type of the key column to a smaller size that meets the index key limit. The following solution uses nvarchar(450), which allows a maximum key length of 450 characters:

create table [misc_info]
( 
    [id] INTEGER PRIMARY KEY IDENTITY NOT NULL, 
    [key] nvarchar(450) UNIQUE NOT NULL, 
    [value] nvarchar(max) NOT NULL
)

Alternative Considerations

If storing non-Unicode characters is not essential, you can consider switching to varchar instead of nvarchar, which increases the maximum key length to 900 characters. However, nvarchar is more appropriate when dealing with characters from multiple code pages.

Remember that these limitations apply specifically to inline indexing. If you utilize external indexing methods, such as creating a covering index on another table, the key column size limit may differ.

The above is the detailed content of Why Does SQLite Throw an 'Invalid Key Column Type for Index' Error, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!