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
----------------------------------------
Schlund + Partner AG
Brauerstraße 48
76135 Karlsruhe
Dipl.-Inf. Sven Woltmann
[EMAIL PROTECTED]
http://www.schlund.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
