List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:May 19 2010 2:59pm
Subject:Re: Foreign Key Problem
View as plain text  
Victor Subervi wrote:
> On Tue, May 18, 2010 at 2:23 PM, Shawn Green <shawn.l.green@stripped>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
> 

AH! that's your mistake. You think that creating the FK will also create 
the column. That does not happen. You have to define the table 
completely before you can associate the columns on this table (the child 
table) with the correct column on the parent table (either Flights or 
Customers).

You need to declare two more fields before you can link them through a 
Foreign Key relationship to a field on another table:

CREATE TABLE PASSENGERS (
   id int auto_increment
, flights_id int not null
, customer_id int not null
... other passenger table columns here ...
, PRIMARY KEY (id)
, FOREIGN KEY (flights_id) REFERENCES Flights(id)
, FOREIGN KEY (customer_id) REFERENCES Customer(id)
) ENGINE=INNODB;

Try it that way and see if it helps.
-- 
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