MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:May 25 2003 7:49am
Subject:Re: foreign key help needed
View as plain text  
Hi!

If you are using MySQL-4.0.13, you can now print detailed FOREIGN KEY error
information with SHOW INNODB STATUS.

----- Original Message ----- 
From: "Cato Liberty" <cato4liberty@stripped>
Newsgroups: mailing.database.mysql
Sent: Sunday, May 25, 2003 12:07 AM
Subject: foreign key help needed


>
> Hello,
>
> I am trying to set up a 2-part table based on foreign keys but running
> into problems. My issue relates to table "Creditors_Customers" that is
> being used to link the "Creditors" and "Customers" tables.
>
> Table Customers: Customer_Id Customer_Address, etc..
> Table Creditors: Creditor_Id Creditor_Address
>
> CREATE TABLE Creditors_Customers(
> Customer_Id bigint( 20 ) NOT NULL default '0',
> Creditor_Id bigint( 20 ) unsigned NOT NULL default '0',
> Debt_Amount varchar( 20 ) default NULL ,
> PRIMARY KEY ( Customer_Id, Creditor_Id ) ,
> FOREIGN KEY ( `Customer_Id` ) REFERENCES `Customers` ( `Customer_Id` ) ON
> DELETE CASCADE ,
> FOREIGN KEY ( `Creditor_Id` ) REFERENCES `Creditors` ( `Creditor_Id` )
> ) TYPE = InnoDB
>
> MySQL said:
>
> Can't create table './admin2_debtsetcustomers/Javaga2.frm' (errno: 150)
>
> This works if I have just Customer_Id as the primary key and as the
> foreign key, and remove Creditor_Id. But if I want both Creditor_Id and
> Customer_Id as the primary key and the foreign key (referenced), I get the
> above error.
>
> What am I doing wrong? Please help!

http://www.innodb.com/ibman.html#InnoDB_foreign_keys
"
there must be an index where the foreign key and the referenced key are
listed as the FIRST columns.
"

Above the column Creditor_Id is not listed as the FIRST column in the
primary key. You have to create another index where it is the first column.

> Thanks!
> Saab

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/


Thread
foreign key help neededCato Liberty24 May
  • Upgrade troublesScott Haneda25 May
    • Re: Upgrade troublesMartin Gainty25 May
      • Re: Upgrade troublesScott Haneda25 May
      • create table question....Bruce Douglas25 May
        • RE: create table question....Bruce Douglas25 May
Re: foreign key help neededHeikki Tuuri25 May