SQL Antipatterns, Volume 1

Using Large Data Types

In standard SQL, you can’t define a PRIMARY KEY, UNIQUE KEY, or a foreign key on a BLOB, CLOB, TEXT, JSON, or ARRAY column.

In MySQL as well, it’s not supported to define a key or an index for these large, variable-size columns, because an indexed data type must be no more than 3072 bytes (or 767 bytes in older versions). You can create a prefix index to make a key or index on the leading bytes of an indexed column. Even though this allows you to create a PRIMARY KEY or UNIQUE KEY on part of a long column, MySQL can’t create a foreign key that references a prefix index, and you get an error.

Foreign-Key-Checklist/text-error.sql

CREATE TABLE Parent (
  parent_id TEXT NOT NULL,
  UNIQUE KEY (parent_id(40)) ...