Error Code: 1215. Cannot add foreign key constraint (foreign keys)

Question

CREATE DATABASE my_db;

CREATE TABLE class (
  classID int NOT NULL AUTO_INCREMENT,
  nameClass varchar(255),
  classLeader varchar(255),
  FOREIGN KEY (classLeader) REFERENCES student(studentID),
  PRIMARY KEY (classID));

CREATE TABLE student (
  studentID int NOT NULL AUTO_INCREMENT,
  lastName varchar(255),
  firstName varchar(255),
  classID int,
  FOREIGN KEY (classID) REFERENCES class(classID),
  PRIMARY KEY (studentID));

I am trying to ensure data consistency between the tables by using foreign key so that the DBMS can check for errors; however, it seems we can't do that for some reason. What's the error and is there an alternative? Also, when I fill a table that has a foreign key, I can't fill the field that's reserved for the foreign key(s), right? Also, is a foreign key considered to be a key at all?

spencer7593 · Accepted Answer · 2013-07-17T05:05:55.877

61

The most likely issue is this line:

FOREIGN KEY (classLeader) REFERENCES student(studentID),

The datatype of classLeader is VARCHAR(255). That has to match the datatype of the referenced column... student.studentID. And of course, the student table has to exist, and the studentID column has to exist, and the studentID column should be the PRIMARY KEY of the student table (although I believe MySQL allows this to be a UNIQUE KEY, rather than a PRIMARY KEY, or even just have an index on it.)

In any case, what's missing here is the output from SHOW CREATE TABLE student;

There's a datatype mismatch.

The classLeader VARCHAR(255) column cannot be a foreign key reference to studentID INT.

The datatypes of the two columns has to match.

edited Jul 17 '13 at 05:05

answered Jul 17 '13 at 04:55

spencer7593

106,611
15
112
140

CREATE DATABASE my_db3; CREATE TABLE class (classID int NOT NULL AUTO_INCREMENT, nameClass varchar(255), classLeader int, FOREIGN KEY (classLeader) REFERENCES student(studentID), PRIMARY KEY (classID)); SHOW CREATE TABLE student (studentID int NOT NULL AUTO_INCREMENT, lastName varchar(255), firstName varchar(255), classID int, FOREIGN KEY (classID) REFERENCES class(classID), PRIMARY KEY (studentID)); – GEORGE Reed Jul 17 '13 at 05:00
1

@GEORGEReed..It would be useful if you can post error you are getting. – Rohan Jul 17 '13 at 05:13
07:25:05 CREATE TABLE class (classID int NOT NULL AUTO_INCREMENT, nameClass varchar(255), classLeader int, FOREIGN KEY (classLeader) REFERENCES student(studentID), PRIMARY KEY (classID)) Error Code: 1215. Cannot add foreign key constraint 0.094 sec – GEORGE Reed Jul 17 '13 at 11:26
2

Thanks a lot. In my case the difference was just that on Table A the field was Int(10) UNSIGNED and on Table B it was Int(10). That's enough for a type mismatch! :o – Ricardo Appleton May 20 '14 at 19:06
@Ricardo Appleton: yes, that's a mismatch. I should have emphasized that the datatypes of the two columns has to match **EXACTLY**. – spencer7593 May 20 '14 at 23:27

score 9 · Answer 2 · edited Dec 03 '19 at 12:38

9

You are getting this error because of in FOREIGN KEY (classLeader) REFERENCES student(studentID) datatype of studentID and classLeader is different.Datatype of primary key column and foreign key column must be same.

From MySQL Site:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must > be the same.

edited Dec 03 '19 at 12:38

Timo Giese

55
9

answered Jul 17 '13 at 05:07

Rohan

3,068
1
20
26

3

+1 This answers the question; this answer identifies the problem causing the error. – spencer7593 Jul 18 '13 at 05:27
I would add that columns using unsigned property can also cause this error. Both columns must have the same definition of signed or unsigned. – Fabiano Araujo Oct 10 '15 at 15:02

score 5 · Answer 3 · answered Aug 25 '17 at 10:51

Though it is a late answer, I hope it would help few people.

I faced the same issue. But, here the Data types were also same.

But, When I checked the create table statement, I found that One table created using a engine called "MyISAM" and another one was using "InnoDB".

SHOW CREATE TABLE <TABLE NAME>

Then I changed both tables engine to "InnoDB" and it worked(I was able create Foreign Key)

score 0 · Answer 4 · edited May 29 '15 at 20:14

The error gets resolved:

To create a Foreign key for a table like this

CREATE TABLE USERS_SO (
    USERNAME VARCHAR(10) NOT NULL,
    PASSWORD VARCHAR(32) NOT NULL,
    ENABLED SMALLINT,
    PRIMARY KEY (USERNAME)
);

The below code works fine

CREATE TABLE AUTHORITIES_SO (
    USERNAME VARCHAR(10) NOT NULL,
    AUTHORITY VARCHAR(10) NOT NULL,
    FOREIGN KEY (USERNAME) REFERENCES USERS_SO(USERNAME)
);

score 0 · Answer 5 · answered May 03 '15 at 08:53

Make sure you have a header and footer in sql dump otherwise you will see all sort of errors when restoring database

Header should look something like below -:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

and footer should look something like below -:

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Hope the above helps

Cheers S

score 0 · Answer 6 · answered Jan 29 '20 at 15:44

0

Along with all the properties mentioned in the other answers, the CHARSET of both tables too should match for successful mapping the foreign keys between them.

answered Jan 29 '20 at 15:44

Adi

361
1
5
23

score 0 · Answer 7 · answered Sep 10 '20 at 07:10

0

just set attributes to UNSIGNED for classID

answered Sep 10 '20 at 07:10

Manoj Kumar

19
2

score 0 · Answer 8 · answered Jan 28 '22 at 00:56

Not directly related to the question but related to title of the question (Error Code: 1215. Cannot add foreign key constraint) - If anyone faces this problem (with correct syntax), make sure there are no generated columns that are using the foreign key column - for me, it took quite a while to figure out it was because of following:

A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

I was using a generated column and using one of these CASCADE options on foreign key constraint

score 0 · Answer 9 · answered Mar 06 '23 at 20:21

0

check the column charset and the cloumn collate, They should be the same in both tables, that is how I fixed mine.

answered Mar 06 '23 at 20:21

Ramzi Haddad

9
1

score -1 · Answer 10 · answered Jan 30 '17 at 18:15

-1

Set end reference point of the key to 'Unique'

answered Jan 30 '17 at 18:15

TheFatMan

1

score -1 · Answer 11 · answered Oct 07 '19 at 11:18

-1

Make sure if yourtable is using InnoDB engine

First, verify if engine is INNODB or not:

SHOW CREATE TABLE yourtable

If not change the engine to InnoDB:

ALTER TABLE yourtable ENGINE=INNODB;

answered Oct 07 '19 at 11:18

Java bee

2,522
1
12
25

score -2 · Answer 12 · edited Feb 19 '18 at 09:33

-2

Remove the Engine, CHARSET, COLLATE from Query , then checkout . It works for me.

edited Feb 19 '18 at 09:33

Abhishek kumar

4,347
8
29
44

answered Feb 19 '18 at 09:14

Niyas

1
1

Error Code: 1215. Cannot add foreign key constraint (foreign keys)

12 Answers12

Linked

Related