Victor Subervi wrote:
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
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
