In InnoDB you *must* put an index on foreign keys. It doesn't do this for you automatically.
You can do it in one statement: create table bar ( bar_id int unsigned auto_increment, foo_id int unsigned, bar_value int, constraint bar_pk primary key (bar_id), index (foo_id), foreign key (foo_id) references foo (foo_id) ) type=innodb; -----Original Message----- From: Jeff Mathis [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:09 AM To: Justin Scheiber Cc: [EMAIL PROTECTED] Subject: Re: help creating foreign keys 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]
