MariaDB won't let me make column unique

Question

I recently duplicated an existing MariaDB table (for use on Amazon RDS). The two tables are basically identical, data included, but I noticed the new version was missing all the unique constraints (kept the primary keys just fine). When I tried to add back the uniques, I got an error:

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

Others have had this problem, and people have claimed that MySQL/MariaDB simply doesn't let you make text columns unique. But my original MariaDB database (on my local machine) has plenty of text columns with unique keys, so that can't be it. I tried their solution anyway (switching to varchar) but MariaDB wouldn't let me do that either because my data has too many characters. Any ideas? Many thanks.

Answer 1

MariaDB 10.4 supports a UNIQUE KEY on a TEXT column because it automatically converts the index to a HASH index:

mysql> create table t (id serial primary key, t text, unique key(t));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t` (`t`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Unfortunately, Amazon RDS for MariaDB does not support MariaDB 10.4 yet (as of 2020-03-19), according to https://aws.amazon.com/rds/mariadb/:

Amazon RDS supports MariaDB Server versions 10.0, 10.1, 10.2, and 10.3 which means that the code, applications, and tools you already use today can be used with Amazon RDS.

The HASH index feature is not supported in MariaDB 10.3 or earlier:

mysql> create table t (id serial primary key, t text, unique key(t));
ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length

You can create a unique key on a prefix of the text column:

mysql> create table t (id serial primary key, t text, unique key(t(1000)));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t` (`t`(1000))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This means the unique key will enforce uniqueness in the first 1000 characters. You won't be allowed to store two rows with string that have the same first 1000 characters, but differ in the 1001st character or later.