Victor Subervi wrote:
> On Tue, May 18, 2010 at 1:09 PM, Shawn Green <shawn.l.green@stripped
> <mailto:shawn.l.green@stripped>> wrote:
>
> Johan De Meersman wrote:
>
>
>
> For additional details about failed FK attempts, check the error
> details in the SHOW INNODB STATUS report.
>
>
> I get this:
>
> 100518 10:26:22 Error in foreign key constraint of table
> seaflight/Passengers:
> constraint foreign key (id) references Flights (flights_id), constraint
> foreign key (id) references Customers (customer_id), name varchar(40),
> weight tinyint(3)) engine=InnoDB:
> Cannot resolve column name close to:
> ), constraint foreign key (id) references Customers (customer_id), name
> varchar(40), weight tinyint(3)) engine=InnoDB
>
> Here's my command:
> create table if not exists Passengers (id int unsigned auto_increment
> primary key, constraint foreign key (id) references Flights
> (flights_id), constraint foreign key (id) references Customers
> (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB;
>
> I think I've got those constraints right. I'm calling the data from
> those other tables, not the other way around. Please advise.
> TIA,
> V
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.
Also,
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
