On Tue, May 18, 2010 at 2:23 PM, Shawn Green <shawn.l.gr...@oracle.com>wrote:
> Shawn Green wrote: > I may be confused but how can the ID of the Passengers table be both the ID > of the Flight they are taking and their Customer ID at the same time? > > http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html > > You may want additional ID columns in the Passengers table to point to the > parent values in those other tables. > Please help me out here. This is what I have: mysql> describe Customers; +-------------+----------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | mysql> describe Flights; +-------------+-------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | create table if not exists Passengers (id int(11) auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; Please help me see where I'm stumbling. All the fields have the same type. If I've got it right, "foreign key (id) references Flights (flights_id)" means that the field "flights_id" will be created in the table Customers and it will reference id in Flights. Trying to interchange those throws an error indicating that flights_id doesn't exist, presumably in Flights. I'm lost, but close to home ;) Please help. V