MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

Question

I have read Database system concepts, 6th edition, Silberschatz. I'm going to implement the university database system shown in chapter 2 on OS X on MySQL. But I have a trouble with creating the table course. the table department looks like

mysql> select * from department
    -> ;
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Elec. Eng. | Taylor   |  85000.00 |
| Finance    | Painter  | 120000.00 |
| History    | Painter  |  50000.00 |
| Music      | Packard  |  80000.00 |
| Physics    | Watson   |  70000.00 |
+------------+----------+-----------+

mysql> show columns from department
    -> ;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | varchar(20)   | NO   | PRI |         |       |
| building  | varchar(15)   | YES  |     | NULL    |       |
| budget    | decimal(12,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

Creating the table course causes the following error.

mysql> create table course
    -> (course_id varchar(7),
    -> title varchar (50),
    -> dept_name varchar(20),
    -> credits numeric(2,0),
    -> primary key(course_id),
    -> foreign key (dept_name) references department);
ERROR 1215 (HY000): Cannot add foreign key constraint

after searching google for foreign key constraint, I have just learned that the word 'foreign key constraint' indicates that data from foreign key column in the table course must exist in primary key column in the table department. But I should have met this error when inserting data.

If not, why does author make me execute that SQL statement?

If I really execute erroneous SQL statement, Does I have to designate dept_name in course table as foreign key after inserting some data?

EDIT : typing set foreign_key_checks=0 into mysql> does not fix the error.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course:
foreign key (dept_name) references department):
Syntax error close to:
)
mysql> set foreign_key_checks=0
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> create table course
    -> (course_id varchar(7),
    -> title varchar(50),
    -> dept_name varchar(20),
    -> credits numeric(2,0),
    -> primary key(course_id),
    -> foreign key (dept_name) references department);
ERROR 1215 (HY000): Cannot add foreign key constraint

all `dept_name` in both table is varchar(15). where Does I have to type `set foreign_key_checks=0`? — inherithandle, Sep 21 '13 at 07:38
typing `set foreign_key_checks=0` into `mysql>` does not fix the error. — inherithandle, Sep 21 '13 at 07:49
says, 2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course: foreign key (dept_name) references department): Syntax error close to: ) — inherithandle, Sep 21 '13 at 07:51
Maybe you want `foreign key (dept_name) references department(dept_name)` — ta.speot.is, Sep 21 '13 at 07:53
Might I trouble you to provide `LATEST FOREIGN KEY ERROR` again, after changing to `departments(dept_name)`? — ta.speot.is, Sep 21 '13 at 08:01
It works with `foreign key (dept_name) references department(dept_name)`. I don't know why author made me execute `foreign key (dept_name) references department)`. — inherithandle, Sep 21 '13 at 08:06
`foreign key (dept_name) references department` is ANSI standard SQL. Maybe the author didn't work with MySQL but with a more standard compliant DBMS. — , Sep 21 '13 at 08:07
if you check syntext in mysql doc, `index_col_name` before and after `REFERENCES tbl_name` is required. — Faishal, Sep 21 '13 at 08:13
Yes, It's my fault. I believed that author give me super SQL working on any database system. — inherithandle, Sep 21 '13 at 08:16
@inherithandle: the author **did** give you correct SQL, it's just MySQL that doesn't comply with the ANSI standard in this regard (and many others) — , Sep 21 '13 at 08:25
To get a detailed error: run `SHOW ENGINE INNODB STATUS` and look at the "LATEST FOREIGN KEY ERROR" section — Jared Beck, Apr 01 '15 at 22:23

score 100 · Answer 1 · edited Jun 08 '16 at 06:07

100

When you get this vague error message, you can find out the more specific error by running

SHOW ENGINE INNODB STATUS;

The most common reasons are that when creating a foreign key, both the referenced field and the foreign key field need to match:

Engine should be the same e.g. InnoDB
Datatype should be the same, and with same length.
e.g. VARCHAR(20) or INT(10) UNSIGNED
Collation should be the same. e.g. utf8
Unique - Foreign key should refer to field that is unique (usually private) in the reference table.

Another cause of this error is:
You have defined a SET NULL condition though some of the columns are defined as NOT NULL.

edited Jun 08 '16 at 06:07

Simon East

55,742
17
139
133

answered Jan 22 '16 at 17:52

am0wa

7,724
3
38
33

4

I just got tripped up because one field was `UNSIGNED` while the other was not, so just edited your answer to include that. Hope that's OK. – Simon East Jun 08 '16 at 05:26
@SimonEast thx for pointing me in the right direction had the same Issue :) – DevJ3rry Dec 22 '16 at 13:25
This should have been the Accepted answer as it's going to be most helpful to people who find this page looking for solutions to their problem – blueimpb Feb 20 '17 at 20:28
2

Got the same problem: if `ON DELETE SET NULL` is used all columns must seem to be nullable. [Here is another question](http://stackoverflow.com/q/33735266/57091). – robsch Mar 06 '17 at 10:07
2

I got this error because the referenced tables were not marked with `ENGINE=InnoDB` - which was also not obvious from the ***very badly*** formatted output of `SHOW ENGINE INNODB STATUS;` – FKEinternet Jun 25 '17 at 04:53
Vaibhav your edit to this answer should be it's own answer – Martin Jan 09 '18 at 14:52
A more verbose output would be SHOW ENGINE INNODB STATUS \G as it 'pivots' the output by 90 degrees and you can more easily scroll the output. – PHZ.fi-Pharazon Feb 18 '20 at 19:22

score 57 · Accepted Answer · edited Dec 31 '13 at 10:02

57

The syntax of FOREIGN KEY for CREATE TABLE is structured as follows:

FOREIGN KEY (index_col_name)
        REFERENCES table_name (index_col_name,...)

So your MySQL DDL should be:

 create table course (
        course_id varchar(7),
        title varchar(50),
        dept_name varchar(20),
        credits numeric(2 , 0 ),
        primary key (course_id),
        FOREIGN KEY (dept_name)
            REFERENCES department (dept_name)
    );

Also, in the department table dept_name should be VARCHAR(20)

More information can be found in the MySQL documentation

edited Dec 31 '13 at 10:02

Alex

8,093
6
49
79

answered Sep 21 '13 at 07:39

Faishal

1,493
1
14
23

I would be happy for you to explain what is that statements in detail. I have never seen the statemnt `CONSTRAINT`. – inherithandle Sep 21 '13 at 07:41
`CONSTRAINT link_dept_course` giving a symbol to this relationship. and it is optional. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, a name for the constraint is created automatically. FYI : http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html – Faishal Sep 21 '13 at 07:48
The constraint part is optional, but the list of PK columns is not. So the `references department` vs. `references department (dept_name)` indeed fixes it. I didn't know that MySQL doesn't support the "short hand" notation (as defined by the SQL standard) – Sep 21 '13 at 08:06
I also discovered that if your table has multiple primary key columns the foreign key constraint will not be able to add. – Abayomi Apetu Aug 10 '18 at 17:10

score 24 · Answer 3 · answered Sep 11 '14 at 12:45

24

Maybe your dept_name columns have different charsets.

You could try to alter one or both of them:

ALTER TABLE department MODIFY dept_name VARCHAR(20) CHARACTER SET utf8;
ALTER TABLE course MODIFY dept_name VARCHAR(20) CHARACTER SET utf8;

answered Sep 11 '14 at 12:45

workflo

351
2
4

That one was tricky. I had one table using explicitly UTF8, not the other. When migrating to a server not using UTF8 as the default, the working schema stopped working. This saved me! – Benoit Duffez Oct 04 '14 at 21:06
Thanks for this answer, in my case the primary key was utf8_bin and the "wannabe-foreign" was utf8_general_ci. This saved me too! – Lucio Crusca Apr 04 '16 at 04:15

score 6 · Answer 4 · answered Sep 21 '13 at 08:20

foreign key (dept_name) references department

This syntax is not valid for MySQL. It should instead be:

foreign key (dept_name) references department(dept_name)

MySQL requires dept_name to be used twice. Once to define the foreign column, and once to define the primary column.

13.1.17.2. Using FOREIGN KEY Constraints

... [the] essential syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:
[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

score 5 · Answer 5 · answered Oct 25 '16 at 22:36

5

It is also possible to get this error if the foreign key is not a primary key within its own table.

I did an ALTER TABLE and accidentally removed the primary key status of a column, and got this error.

answered Oct 25 '16 at 22:36

Jack Davidson

4,613
2
27
31

score 5 · Answer 6 · answered May 18 '17 at 22:07

ERROR 1215 (HY000): Cannot add foreign key constraint

It is also worth noting that you get this error when the type of the column that is a foreign key in another able doesn't explicitly match the column in the correct table.

For example:

alter table schoolPersons
         add index FKEF5AB5E532C8FBFA (student_id),
         add constraint FKEF5AB5E532C8FBFA
         foreign key (student_id)
         references student (id);
ERROR 1215 (HY000): Cannot add foreign key constraint

This was because the student_id field was defined as:

mysql> desc schoolPersons;
+--------------------+------------+------+-----+---------+----------------+
| Field              | Type       | Null | Key | Default | Extra          |
+--------------------+------------+------+-----+---------+----------------+
| student_id         | bigint(20) | YES  |     | NULL    |                |

while the id field in the student table was defined as:

mysql> desc persons;
+--------------+----------------------+------+-----+-------------------+-----------------+
| Field        | Type                 | Null | Key | Default           | Extra           |
+--------------+----------------------+------+-----+-------------------+-----------------+
| id           | int(10) unsigned     | NO   | PRI | NULL              | auto_increment  |

The bigint(20) (generated from Java long by hibernate) is not compatible with int(10) unsigned (Java int).

score 3 · Answer 7 · answered Nov 20 '17 at 12:20

I don't meet the problem as you. But I get the same ERROR Message. So I mark it down here for others' convience.

Check the charset of two table if the column type is char or varchar. I use a charset=gbk, but I create a new table whose default charset=utf8. So the charset is not the same.

ERROR 1215 (HY000): Cannot add foreign key constraint

To solve it is to use the same charset. For example utf8.

score 2 · Answer 8 · edited Mar 04 '17 at 00:19

2

Just add 'unsigned' for the FOREIGN constraint

`FK` int(11) unsigned DEFAULT NULL,

edited Mar 04 '17 at 00:19

Pang

9,564
146
81
122

answered Mar 02 '17 at 10:09

vedavyasa k

1,173
1
7
4

score 1 · Answer 9 · answered Jan 12 '16 at 23:58

1

It's worth noting that this error can also happen if the target table or column you're using in the REFERENCES portion simply doesn't exist.

answered Jan 12 '16 at 23:58

Alkanshel

4,198
1
35
54

score 1 · Answer 10 · answered Nov 01 '17 at 10:55

1

Below code worked for me

set @@foreign_key_checks=0;
ALTER TABLE  `table1` ADD CONSTRAINT `table1_fk1` FOREIGN KEY (`coloumn`) REFERENCES `table2` (`id`) ON DELETE CASCADE;

answered Nov 01 '17 at 10:55

anjaneyulubatta505

10,713
1
52
62

score 1 · Answer 11 · answered Aug 23 '20 at 17:59

I had this error when I tried to import (in MysqlWorkbench) from a PhpAdminMySQL export. After verifying I had disabled the unique keys and foreign keys with:

SET unique_checks=0;
SET foreign_key_checks = 0;

I still get the same error (MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint). The error occurred on this create statement.

DROP TABLE IF EXISTS `f1_pool`;
CREATE TABLE `f1_pool` (
 `id` int(11) NOT NULL,
 `name` varchar(45) NOT NULL,
 `description` varchar(45) DEFAULT NULL COMMENT 'Optional',
 `ownerId` int(11) NOT NULL,
 `lastmodified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

No foreign key or unique index, so what is wrong here? Finally (after 90 minutes puzzling) I decided to restart MySQL and do the import again with one modification: I dropped all tables before doing the import. And there was no error, all functioned, tables and views restored. So my advice, if all looks ok, first try to restart MySQL!

score 1 · Answer 12 · answered Nov 07 '21 at 18:51

1

In my case the engine is different between the parent table and the child table. Making the engines equal is working

The problem: parent table 'engine' => 'MyISAM', child table 'engine' => 'innoDB',

Correção: parent table 'engine' => 'MyISAM', child table 'engine' => 'MyISAM',

answered Nov 07 '21 at 18:51

Anderson Barros

11
2

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/30281310) – JohnH Nov 09 '21 at 19:55

score 0 · Answer 13 · answered Apr 06 '18 at 16:00

I don't see anyone stating this explicitly and I had this same error message and my problem was that I was trying to add a foreign key to a TEMPORARY table. Which is disallowed as noted in the manual

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables.

(emphasis mine)

score 0 · Answer 14 · answered May 18 '18 at 04:49

I came across the same issue as well. Not sure why this is working but it indeed works: Try add ENGINE INNODB after your create query.

mysql> create table course
-> (course_id varchar(7),
-> title varchar (50),
-> dept_name varchar(20),
-> credits numeric(2,0),
-> primary key(course_id),
-> foreign key (dept_name) references department) ENGINE INNODB;

score 0 · Answer 15 · answered Mar 01 '19 at 08:20

Even if this is not directly linked precisely to your situation, it may help further readers to note that you can get exactly the same error output when you type show engine innodb mstatus if you do not respect the order of creating the database tables; meaning you must not add a foreign constraint referencing a table that does not exist yet. The reference table must exist prior to the table which points to it.

This is also true when the table creation order is respected but not the columns involved in the foreign key constraint.

score 0 · Answer 16 · answered Apr 17 '19 at 03:34

0

In my case charset, datatype every thing was correct. After investigation I found that in parent table there was no index on foreign key column. Once added problem got solved.

answered Apr 17 '19 at 03:34

DEV

2,106
3
25
40

score 0 · Answer 17 · answered Dec 01 '21 at 18:37

0

Be aware it can be several different things.

In my case it was the table/field collation:

FK target: CHARSET=utf8 COLLATE=utf8_unicode_ci
FK source field: CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

answered Dec 01 '21 at 18:37

Fabrício José Souza

26
2

score 0 · Answer 18 · answered Nov 01 '22 at 18:35

0

Because related tables should have the same config about CHARSET and ENGINE. Execute:

show table status from <DATABASE_NAME>;

See Engine and Collation about your table. When you create put after ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

answered Nov 01 '22 at 18:35

Roberto Góes

778
1
13
20

score -1 · Answer 19 · edited Sep 06 '16 at 10:20

-1

CONSTRAINT vendor_tbfk_1 FOREIGN KEY (V_CODE) REFERENCES vendor (V_CODE) ON UPDATE CASCADE

this is how it could be... look at the referencing column part. (V_code)

edited Sep 06 '16 at 10:20

agua from mars

16,428
4
61
70

answered Sep 06 '16 at 07:53

naube

1

MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

19 Answers19

13.1.17.2. Using FOREIGN KEY Constraints

Linked