getnathans

Hello folks This error is killing me and I don't understand why it is occurring. Here is the scenario. I have a table called "certificate_types" which has ID column and certificate_type column. The ID is the primary key. I also have a table called "jobs" which has an ID column. It is a the primary key of jobs table. Jobs table also has few more columns such as title_of_job, description..etc to define the required characteristics of a typical job. Now, some recruiters want to specify that some of their jobs need certain certificates. For an examples, coffee maker job in a school needs barista certificate as well as working with children certificate. So I identified this situation as one to many relationship since one job can have multiple certificate requirements. Therefore, I created a separate table called "required_certificates" using following SQL command. CREATE TABLE requiredcertificates ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, jobid INT(11) UNSIGNED, certificateid INT(11) UNSIGNED, compulsory Boolean DEFAULT false, desirable Boolean DEFAULT true, workingtowards Boolean DEFAULT false, CONSTRAINT requiredcertificates_jobid_foriegn FOREIGN KEY (jobid) REFERENCES jobs (id), CONSTRAINT requiredcertificates_certificateid_foriegn FOREIGN KEY (certificateid) REFERENCES certificatetypes (id) ); I get #1215 - Cannot add foreign key constraint error. I can guarantee that all the tables are innodb and id column in jobs table and id column in certificates table are indexed. But following one runs without any errors. CREATE TABLE requiredcertificates ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, jobid INT(11) UNSIGNED, certificateid INT(11) UNSIGNED, compulsory Boolean DEFAULT false, desirable Boolean DEFAULT true, workingtowards Boolean DEFAULT false, CONSTRAINT requiredcertificates_certificateid_foriegn FOREIGN KEY (certificateid) REFERENCES certificatetypes (id) ); Therefore I know for sure the error is with jobs table. I also ran SHOW ENGINE INNODB STATUS and following written under LATEST FOREIGN KEY ERROR ------------------------ 2015-03-01 23:33:26 19c8 Error in foreign key constraint of table jobbadb/#sql-1f30_59c: FOREIGN KEY (`jobid`) REFERENCES `jobbadb`.`job`(`id`) ON DELETE CASCADE ON UPDATE CASCADE: 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. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition. Can any of you guys tell me whats going on? Many thanks Cheers Nathan