MySQL Refuses a Key on a TEXT Column Without a Prefix Length
A TEXT column as a primary key is ordinary on SQLite and PostgreSQL. MySQL and MariaDB refuse it, and MySQL refuses a plain index or unique key on TEXT as well.
SQLite and PostgreSQL index TEXT columns as they are. MySQL wants a prefix length, INDEX (x(100)), which the others reject. MariaDB 10.4 and later accept plain indexes and unique keys on TEXT columns.
What Each Engine Answered
The example
CREATE TABLE t (x TEXT PRIMARY KEY);
SELECT COUNT(*) FROM t;
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 0 |
| PostgreSQL 16.15 | 0 |
| MySQL 8.0.46 | error: BLOB/TEXT column 'x' used in key specification without a key length |
| MariaDB 10.11.14 | error: BLOB/TEXT column 'x' used in key specification without a key length |
What to Write Instead
This form answers the same on every engine:
CREATE TABLE t (x VARCHAR(255) PRIMARY KEY);
SELECT COUNT(*) FROM t;
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 0 |
| PostgreSQL 16.15 | 0 |
| MySQL 8.0.46 | 0 |
| MariaDB 10.11.14 | 0 |
Use VARCHAR(n) for columns you index.
What BareSQL Portable Reports
Saved as example.sql, the example gets this from baresql lint --all:
$ baresql lint --all example.sql
example.sql:1:17: error: MySQL refuses an index, unique key or primary key on a TEXT or BLOB column without a prefix length, and MariaDB refuses the primary key. [index-on-text]
x TEXT PRIMARY KEY
Use VARCHAR(n) for columns you index.
1 problem: 1 error.
baresql rules index-on-text prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.