Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the FIRST columns.
InnoDB does not auto-create indexes on foreign keys or referenced keys:
you have to create them explicitly.So you need to have an index on the column foo_id in table bar.
If you're running MySQL 4.0.13 you can get information on the last cause of a foreign key error using show innodb status. When working with InnoDB, and particularly features specific to InnoDB, I'd strongly recommend reading the InnoDB manual on innodb.com, as it covers most common issues like this one.
Chris
Justin Scheiber wrote:
Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible. Do I need to rethink the table structure? Or do I just not understand something here?
create table foo ( foo_id int unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb;
create table bar ( bar_id int unsigned auto_increment, foo_id int unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb;
ERROR 1005: Can't create table './test/bar.frm' (errno: 150)
-justin
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
