Sven, are you using the UTF8 charset? Then a single character may use up to 3 bytes. Since MySQL cannot work with index columns longer than 255 bytes, for columns CHAR(86) or longer, MySQL must define a 'column prefix' index, if you define an index on the column. That is, MySQL internally creates an index of type INDEX (colname(85)) instead of an index on the full column. And FOREIGN KEYs do not work on 'column prefix' indexes :(.
A workaround: use latin1 or latin1_german1_ci. Then 1 character only takes 1 byte. In the future, MySQL will get longer CHAR and VARCHAR columns. That will alleviate this problem. The current limit 255 bytes is rather short. I may also improve the foreign key check algorithm so that it can work also on column prefix indexes. http://www.mysql.com/doc/en/Charset-Unicode.html : " The UTF8 character set (transform Unicode representation) is an alternative way to store Unicode data. It is implemented according to RFC2279. The idea of the UTF8 character set is that various Unicode characters fit into byte sequences of different lengths. Basic Latin letters, digits, and punctuation signs use one byte. Most European and Middle East script letters fit into a two-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others. Korean, Chinese and Japanese ideographs use three-byte sequences. Currently, MySQL UTF8 support does not include four-byte sequences. " Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ............ List:MySQL General Discussion« Previous MessageNext Message » From:Sven WoltmannDate:February 10 2004 1:12am Subject:Foreign Key Reference to a VARCHAR Hi, I hope this is not a well known problem since I just signed up to this list. But I checked the February archive and couldn't find anything on this. I was trying for a couple of hours now to create a foreign key reference on a varchar field: CREATE TABLE users ( login VARCHAR(20) NOT NULL, password VARCHAR(20) NOT NULL, email_address VARCHAR(100) NOT NULL, -- PRIMARY KEY(login), INDEX(email_address) ) TYPE = InnoDB; create table email_alias ( alias VARCHAR(100) NOT NULL, email_address VARCHAR(100) NOT NULL, -- PRIMARY KEY(alias), INDEX(email_address), FOREIGN KEY (email_address) references users(email_address) ) TYPE = InnoDB; Actually my tables were a lot bigger, but I stripped them down to these short tables to resolve my problem, which is: When creating the second table, I get the error message: ERROR 1005 (HY000): Can't create table './test/email_alias.frm' (errno: 150) I admit, the first time I didn't put an INDEX on email_address. I figured that out quite fast. Then I searched again in the Newsgroups and did a SHOW INNODB STATUS. I got the following message: "Cannot find an index in the referenced table where the referenced columns appear as the first columns [...]" Well - this didn't help at all :( So I changed my table definitions a hundred times to find out what exactly the problem was. And after hours, I found out: The VARCHAR must not be longer than 85 characters. If you replace the "100" in the example above by a "85", IT WORKS!!! So... have I missed the fine print in the documentation or did I actually find a bug? Here's my configuration: - 4.1.1-alpha-standard-log - Official MySQL-standard binary - i686 - pc-linux (debian 3.0 woody) Sven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
