2

I encounter an error when trying to create a compound unique index using a foreign key and a TEXT column.

The error can avoided by explicitly adding an index on the foreign key or changing the type of the TEXT column to varchar(50) as well as other types.

CREATE DATABASE odd;
USE odd;

# friends table is not directly relevant to question
CREATE TABLE friends(
  pk int NOT NULL,
  PRIMARY KEY (pk)
);

CREATE TABLE works1(
  friendfk int NOT NULL,
  word text NOT NULL,
  KEY friendfk (friendfk), # indexed column
  FOREIGN KEY (friendfk) REFERENCES friends (pk)
);


CREATE TABLE fails(
  friendfk int NOT NULL,
  word text NOT NULL, # using TEXT and and left off index
  FOREIGN KEY (friendfk) REFERENCES friends (pk)
);
#these seem to have functionally identical output
SHOW CREATE TABLE works1;
SHOW CREATE TABLE fails;
# no error: indexed `friendfk` and `word` column is TEXT
CREATE UNIQUE INDEX idx_works1 ON works1 (friendfk, word);

# errors: Error on rename of './odd/#sql-alter-2d0-4' to './odd/fails' 
# (errno: 150 "Foreign key constraint is incorrectly formed")
# no index on `friendfk` and `word` is a TEXT column
CREATE UNIQUE INDEX idx_fails ON fails (friendfk, word); 

The error from running SHOW ENGINE INNODB STATUS;

LATEST FOREIGN KEY ERROR
------------------------
2023-10-02 01:18:13 0x7fc9c8174700 Error in foreign key constraint of table odd/fails:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT `fails_ibfk_1` FOREIGN KEY (`friendfk`) REFERENCES `friends` (`pk`)

These table structures also accept the unique index without error

CREATE TABLE works2(
  friendfk int NOT NULL,
  word varchar(50) NOT NULL, # changed type from TEXT and left off index
  FOREIGN KEY (friendfk) REFERENCES friends (pk)
);

CREATE TABLE works3(
  friendfk int NOT NULL,
  word text NOT NULL # using TEXT but no foreign keys
);

# no error: no index on `friendfk` and `word` is varchar(40)
CREATE UNIQUE INDEX idx_works2 ON works2 (friendfk, word);
# no error: no foreign key and 'word' is TEXT
CREATE UNIQUE INDEX idx_works3 ON works3 (friendfk, word);

There are a few things that confuse me from this.

  1. I thought a foreign key column was already considered an indexed column. Am I mistaken?
  2. the output for SHOW CREATE TABLE for both works1 and fails is functionally identical. For both of them, it shows friendfk as a key already. How can I identify what is actually missing?
  3. Why does changing the type of word to a varchar allow the unique index to be created without error? According to https://mariadb.com/kb/en/getting-started-with-indexes/#unique-index , I should be allowed to use use a TEXT column for an unique index, and for works3 I am able to do that.

Here's a fiddle link https://dbfiddle.uk/yHOhIfGV

1 Answer 1

0

TEXT (and BLOG) columns may not be used in indexes or FOREIGN KEYs. This is simply a limitation.

A column called "word" probably does not need to be so big as to need a TEXT declaration. Instead, use a VARCHAR with a reasonable size limitation.

A FOREIGN KEY implicitly creates an index if no suitable index already exists.

The above points applies whether a single column or multiple columns are involved in the FK and/or Index.

4
  • The first part of your answer seems to be untrue for unique indexes since 10.4. See dbfiddle.uk/_qjN9hw- and mariadb.com/kb/en/changes-and-improvements-in-mariadb-10-4/… In regards to foreign key implicitly creating an index, I'm having to explicitly create an index on friendfk on fails, and it is not clear why I am having to add it explicitly since it should be as you say added implicitly. See dbfiddle.uk/hKTYmEfE
    – joelliusp
    Oct 2, 2023 at 13:08
  • @joelliusp - That link is old. Some problems did not get resolved until 2023. The fixes were backfilled into 10.4.28. jira.mariadb.org/browse/MDEV-27653. A replication problem was not fixed until 10.4.32 - jira.mariadb.org/browse/MDEV-32093 . However, it is unclear whether collation is relevant to the OP's question. FKs expect to work with BTrees, not the 'hidden' Hashes that are being for TEXT UNIQUE indexes.
    – Rick James
    Oct 3, 2023 at 0:47
  • Or maybe the Hash implementation does not work well with composite indexes.
    – Rick James
    Oct 3, 2023 at 0:51
  • wouldn't the link being old be irrelevant since this is newish functionality? Or are you suggesting that the composite index hash functionality is not fully fleshed out?
    – joelliusp
    Oct 3, 2023 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.