I am having this problem, which has been addressed in many posts, but none of them show where I am going wrong. I get the error 1005 with errno 150 when I try the following;
structure of appointments:
CREATE TABLE `appointments` (
`appointmentID` int(11) NOT NULL AUTO_INCREMENT,
`apptNum` tinyint(3) unsigned NOT NULL DEFAULT '1',
`cwaID` int(11) NOT NULL DEFAULT '0',
`siteID` int(11) DEFAULT NULL,
`titleCode` varchar(25) NOT NULL,
`apptPercentage` double DEFAULT '0',
`status` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
`payRate` double DEFAULT '0',
`rateCode` varchar(5) CHARACTER SET utf8 DEFAULT NULL,
`locationInfo` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`apptStartDate` date DEFAULT NULL,
`apptEndDate` date DEFAULT NULL,
`isPrimaryAppointment` varchar(1) CHARACTER SET utf8 NOT NULL
DEFAULT 'y',
`lastModDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`appointmentID`),
UNIQUE KEY `appointmentID` (`appointmentID`),
UNIQUE KEY `cwaID-apptNum` (`cwaID`,`apptNum`),
KEY `apptNum` (`apptNum`),
KEY `siteID` (`siteID`),
KEY `rateCode` (`rateCode`),
KEY `cwaID` (`cwaID`),
KEY `titleCode` (`titleCode`),
CONSTRAINT `FK_employees_appointments` FOREIGN KEY (`cwaID`) REFERENCES
`employees` (`cwaID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
structure of jobtitles:
CREATE TABLE `jobtitles` (
`titleCode` varchar(25) CHARACTER SET utf8 NOT NULL,
`jobTitle` varchar(255) CHARACTER SET utf8 NOT NULL,
`unitID` varchar(10) CHARACTER SET utf8 NOT NULL,
`scope` varchar(10) CHARACTER SET utf8 NOT NULL,
`payPeriod` char(1) NOT NULL DEFAULT 'M',
`maxPay` decimal(8,2) NOT NULL DEFAULT '0.00',
`minPay` decimal(8,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`titleCode`),
KEY `jobTitle` (`jobTitle`),
KEY `scope` (`scope`),
KEY `unitID` (`unitID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I want to add a foreign key to appointments for the titleCode field:
ALTER TABLE appointments ADD CONSTRAINT `FK_jobtitles_appointments`
FOREIGN KEY (`titleCode`) REFERENCES `jobtitles` (`titleCode`);
but this attempt generates the ERROR 1005 with errno 150, which implies a badly formed foreign key.
Any suggestions?