Multiple column foreign key
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