yes, quite right. thanks for pointing that out. jeff
Kevin Fries wrote: > > 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] -- 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]
