From: Shawn Green
Date: May 19 2010 4:02pm
Subject: Re: Foreign Key Problem
List-Archive: http://lists.mysql.com/mysql/221622
Message-Id: <4BF40BAF.80504@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Victor Subervi wrote:
> On Wed, May 19, 2010 at 10:59 AM, Shawn Green <shawn.l.green@stripped>wrote:
> 
>> Shawn Green wrote:
>>
>> 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.
>>
>> Well, that was certainly one error, but there's another:
> 
> ------------------------
> LATEST FOREIGN KEY ERROR
> ------------------------
> 100519  8:46:10 Error in foreign key constraint of table
> seaflight/Passengers:
> foreign key (id) references Flights (flights_id), foreign key (id)
> references Customers (customer_id), name varchar(40), weight tinyint(3))
> engine=InnoDB:
> Cannot resolve column name close to:
> ), foreign key (id) references Customers (customer_id), name varchar(40),
> weight tinyint(3)) engine=InnoDB
> 
> Now, I've added the innodb engine to all tables and constraints on the id
> columns of the foreign key tables. What else?
> TIA,
> V
> 
look again closely at your FK definitions. The pattern should be

FOREIGN KEY (child_table_column) REFERENCES 
parent_table(parent_table_column)

Yours appears to be something else.


-- 
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN