List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:May 18 2010 6:23pm
Subject:Re: Foreign Key Problem
View as plain text  
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
Thread
Foreign Key ProblemVictor Subervi18 May
  • Re: Foreign Key ProblemJohan De Meersman18 May
    • Re: Foreign Key ProblemVictor Subervi18 May
      • Re: Foreign Key ProblemJohan De Meersman18 May
        • Re: Foreign Key ProblemShawn Green18 May
          • Re: Foreign Key ProblemVictor Subervi18 May
            • Re: Foreign Key ProblemShawn Green18 May
              • Re: Foreign Key ProblemVictor Subervi19 May
                • Re: Foreign Key ProblemShawn Green19 May
                  • Re: Foreign Key ProblemVictor Subervi19 May
                    • Re: Foreign Key ProblemShawn Green19 May
                      • Re: Foreign Key ProblemVictor Subervi20 May
                        • Re: Foreign Key ProblemVictor Subervi22 May