ERROR 1005 (HY000) errno: 150

271 Views Asked by At

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?

0

There are 0 best solutions below