List:General Discussion« Previous MessageNext Message »
From:Victor Subervi Date:May 19 2010 2:03pm
Subject:Re: Foreign Key Problem
View as plain text  
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

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