CODEWITHSUNDEEP
mysqldatabase
GEORGE Reed
GEORGE Reed

Reputation: 445

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

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?

Upvotes: 44

Views: 103678

Answers (12)

Ramzi Haddad
Ramzi Haddad

Reputation: 9

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

Upvotes: 0

gawkface
gawkface

Reputation: 2312

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

Upvotes: 0

Manoj Kumar
Manoj Kumar

Reputation: 19

just set attributes to UNSIGNED for classID

Upvotes: 0

Adi
Adi

Reputation: 361

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.

Upvotes: 0

Rohan
Rohan

Reputation: 3078

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.

Upvotes: 9

Java wanna bee
Java wanna bee

Reputation: 3109

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;

Upvotes: -1

Niyas
Niyas

Reputation: 1

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

Upvotes: -2

John Prawyn
John Prawyn

Reputation: 1663

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)

Upvotes: 7

TheFatMan
TheFatMan

Reputation: 1

Set end reference point of the key to 'Unique'

Upvotes: -1

MS Ibrahim
MS Ibrahim

Reputation: 1789

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)
);

Upvotes: 0

stevensagaar
stevensagaar

Reputation: 58

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

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

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.

Upvotes: 61

Related Questions