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]

Reply via email to