EC
1170
MariaDBERRORCommonDDLHIGH confidence

BLOB/TEXT column used in key specification without a key length

Production Risk

Low — DDL fails; no data loss.

What this means

ER_BLOB_KEY_WITHOUT_LENGTH (1170, SQLSTATE 42000) is raised when a BLOB or TEXT column is included in a PRIMARY KEY or UNIQUE index without specifying a prefix length.

Why it happens
  1. 1TEXT or BLOB column added to a PRIMARY KEY without a prefix length
  2. 2UNIQUE constraint on a TEXT column without specifying key length
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (content TEXT, PRIMARY KEY (content));

expected output

ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length

Fix

Add a prefix length to the index specification

WHEN A TEXT/BLOB column must be part of an index.

Add a prefix length to the index specification
CREATE TABLE t (content TEXT, INDEX idx_content (content(255)));
-- For a natural key use a hash column instead:
CREATE TABLE t (
  content TEXT,
  content_hash CHAR(64) AS (SHA2(content, 256)) STORED,
  INDEX idx_hash (content_hash)
);

Why this works

Prefix indexes limit the indexed portion of the column to a fixed byte count; a generated hash column provides a fixed-length unique key without the prefix limitation.

What not to do

✕ Use TEXT columns as primary keys

TEXT as primary key requires a prefix (which truncates uniqueness) and is much slower than an integer surrogate key.

Sources
Official documentation ↗

MySQL 8.0 — 1170 ER_BLOB_KEY_WITHOUT_LENGTH

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors