MySQL Bugs: #8429: Can't create foreign keys on TINYTEXT fields

Bug #8429	Can't create foreign keys on TINYTEXT fields
Submitted:	11 Feb 2005 0:34	Modified:	25 Feb 2005 18:09
Reporter:	Eric Schwartz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1.9	OS:	Linux (Debian unstable ('sid'))
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I have two tables defined as follows:

CREATE TABLE `A` (
`email` tinytext NOT NULL,
`name` tinytext,
PRIMARY KEY (`email`(255))
) TYPE=InnoDB;

CREATE TABLE `B` (
`other_email` tinytext NOT NULL,
`happylevel` int(11) default '0',
KEY `b_other_idx` (`other_email`(255))
) TYPE=InnoDB;

I then try to create a foreign key on B to reference 'email' in table A:

mysql> alter table `B` add foreign key `b_other_fk` (`other_email`) references `A` (`email`);
ERROR 1170 (42000): BLOB/TEXT column 'other_email' used in key specification without a key length

However, if I define them as follows:

CREATE TABLE `A` (
  `email` varchar(128) NOT NULL default '',
  `name` varchar(128) default NULL,
  PRIMARY KEY  (`email`)
) ENGINE=InnoDB ;

CREATE TABLE `B` (
  `other_email` varchar(128) NOT NULL default '',
  `something` int(11) default NULL,
  KEY `b_oe_idx` (`other_email`(128)),
) ENGINE=InnoDB;

then creating the foreign key works fine:

mysql> alter table B add foreign key `b_oe_fk` (`other_email`) REFERENCES `A` (`email`); 
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
1) create the tables as specified above, with the keys, but without any foreign keys
2) Try to create the foreign key as above.
3) cry, when it doesn't work.

Suggested fix:
Either explicitly disclaim support for foreign keys on TEXT types in the docs, or (preferably) support them.

SQLSTATE 42962 doesn't allow blob fields to be part of foreign keys.  I changed the category of this bug to documentation.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
to point out explicitly that BLOB/TEXT columns can't be included in foreign keys. (The
reason is that FK columns cannot have an index prefix, and BLOB/TEXT must include
an index prefix.)

It appears in one of the examples in this bug report that it was possible to include
a column with an index prefix in the FK definition. But what was really happening
is that the prefix was being lost because it was the same as the column length. As
a result, the column appeared not to have a prefix. I have filed a bug report about
this: http://bugs.mysql.com/bug.php?id=8814

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25750