Not able to create foreign key
Posted by:
Jayraj
Date: January 16, 2006 03:08AM
Hi,
I am not able to create a foreign key in table employeedept
Script for this table is
CREATE TABLE `employeedept` (
`EID` bigint(20) unsigned NOT NULL,
`DEPTID` bigint(20) unsigned NOT NULL,
`JOININGDATE` datetime NOT NULL,
`EDEPTSTATUS` char(1) character set ucs2 NOT NULL,
`LEAVING DATE` date default NULL,
`SUID` bigint(20) unsigned default NULL,
PRIMARY KEY (`EID`,`DEPTID`,`JOININGDATE`),
CONSTRAINT `employeedept_ibfk_1` FOREIGN KEY (`EID`) REFERENCES `employee` (`EID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Log is showing this message:
060116 13:46:36 Error in foreign key constraint of table employee/#sql-160c_1e:
foreign key `FK_employeedeptsss`(`EID`) references `employee` (`EID`) on delete no action on update no action:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please provide a feasible solution.
Thanks in advance,
Jayraj
Tables on which above mentioned table is Dependent are:
CREATE TABLE `department` (
`DEPTID` bigint(20) unsigned NOT NULL auto_increment,
`PDEPTID` bigint(20) unsigned NOT NULL,
`DEPTNAME` varchar(45) NOT NULL,
PRIMARY KEY (`DEPTID`,`DEPTNAME`),
KEY `DEPTID` (`DEPTID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `employee` (
`EID` bigint(20) NOT NULL,
`ENAME` varchar(30) NOT NULL,
`BIRTHDATE` date NOT NULL,
`JOININGDATE` date NOT NULL,
`STATUS` char(1) NOT NULL default 'Y',
`LEAVINGDATE` date default NULL,
PRIMARY KEY (`EID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;