the syntax for foreign key creation is different than what you have. You need an alter table statement.
create table bar ( bar_id int unsigned auto_increment primary key, foo_id int unsigned, bar_value int, ) type=innodb; alter table bar add constraint foreign key (foo_id) references foo(foo_id) on delete cascade; 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] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
