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:
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1.9 OS:Linux (Debian unstable ('sid'))
Assigned to: Paul DuBois CPU Architecture:Any

[11 Feb 2005 0:34] Eric Schwartz
I have two tables defined as follows:

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

`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:

  `email` varchar(128) NOT NULL default '',
  `name` varchar(128) default NULL,
  PRIMARY KEY  (`email`)

  `other_email` varchar(128) NOT NULL default '',
  `something` int(11) default NULL,
  KEY `b_oe_idx` (`other_email`(128)),

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.
[11 Feb 2005 1:55] Jorge del Conde
SQLSTATE 42962 doesn't allow blob fields to be part of foreign keys.  I changed the category of this bug to documentation.
[25 Feb 2005 18:09] Paul DuBois
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

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
[8 Jun 2005 10:51] Bugs System
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: