#1215 - Cannot add foreign key constraint

[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

Edited March 1, 2015 by getnathans

[kicken]

It'd help if you posted the definitions of all the tables. Make sure that the column types match and you've set your ID column as the primary key.