Skip navigation links

MySQL Forums :: Foreign Keys (Referential Integrity) :: Multiple column foreign key


Advanced Search

Multiple column foreign key
Posted by: Shashwat Tripathi ()
Date: July 18, 2011 06:03AM

I have two table named 'customers' and 'accounts' with following create definitions:

delimiter $$

CREATE TABLE `customers` (
`CID` int(10) unsigned NOT NULL,
`First_Name` varchar(30) NOT NULL,
`Last_Name` varchar(30) default NULL,
`Father_Name` varchar(45) default NULL,
`Address_Res` varchar(100) default NULL,
`Phone` varchar(60) default NULL,
`Mobile` varchar(60) default NULL,
`Comment` varchar(200) default NULL,
`Register_Date` datetime NOT NULL,
PRIMARY KEY (`CID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to store Customer informatios'$$

delimiter $$

CREATE TABLE `accounts` (
`CID` int(10) unsigned NOT NULL,
`ACN` int(10) unsigned NOT NULL,
`Loan_Amount` int(10) unsigned NOT NULL,
`Interest_Rate` decimal(10,0) unsigned NOT NULL,
`Opening_Date` date NOT NULL,
`Closing_Date` date NOT NULL,
`Comment` varchar(200) default NULL,
`DB_Save_Date` datetime NOT NULL,
KEY `INDEX_Accounts_ACN` (`ACN`),
KEY `FK_Accounts` (`CID`),
CONSTRAINT `FK_Accounts` FOREIGN KEY (`CID`) REFERENCES `customers` (`CID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

========================
Now I want to create another table 'Mortgages' with following definition

CREATE TABLE `bankpage_`.`Mortgages` (

`CID` INT UNSIGNED NOT NULL ,

`ACN` INT UNSIGNED NOT NULL ,

`Type` VARCHAR(45) NOT NULL ,

`Weight` DECIMAL UNSIGNED NOT NULL ,

`Mortgage_Date` DATE NOT NULL ,

`Release_Date` DATE NOT NULL ,

`IsSeized` TINYINT(1) NOT NULL ,

`Comments` VARCHAR(45) NULL ,

INDEX `FK_Mortgages` (`CID` ASC, `ACN` ASC) ,

CONSTRAINT `FK_Mortgages`

FOREIGN KEY (`CID` , `ACN` )

REFERENCES `bankpage_`.`accounts` (`CID` , `ACN` )

ON DELETE NO ACTION

ON UPDATE NO ACTION);

=================
On execution I am getting the following error:
ERROR 1005: Can't create table '.\bankpage_\mortgages.frm' (errno: 150)

================
Please help me
I want to reference two columns of mortages table to accounts table

Options: ReplyQuote


Subject Views Written By Posted
Multiple column foreign key 3733 Shashwat Tripathi 07/18/2011 06:03AM
Re: Multiple column foreign key 1315 Devart Team 07/20/2011 12:54AM
Re: Multiple column foreign key 1133 Shashwat Tripathi 07/20/2011 01:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.