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

MysqlSqlDatabase

Mysql Problem Overview


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

Mysql Solutions


Solution 1 - Mysql

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.

Solution 2 - Mysql

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

Solution 3 - Mysql

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;

Solution 4 - Mysql

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

Solution 5 - Mysql

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.

Solution 6 - Mysql

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

Solution 7 - Mysql

Just add 'unsigned' for the FOREIGN constraint

`FK` int(11) unsigned DEFAULT NULL,

Solution 8 - Mysql

> 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.

Solution 9 - Mysql

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.

Solution 10 - Mysql

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;

Solution 11 - Mysql

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',

Solution 12 - Mysql

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)

Solution 13 - Mysql

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;

Solution 14 - Mysql

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.

Solution 15 - Mysql

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.

enter image description here

Solution 16 - Mysql

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!

Solution 17 - Mysql

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

Solution 18 - Mysql

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)

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestioninherithandleView Question on Stackoverflow
Solution 1 - Mysqlam0waView Answer on Stackoverflow
Solution 2 - MysqlFaishalView Answer on Stackoverflow
Solution 3 - MysqlworkfloView Answer on Stackoverflow
Solution 4 - Mysqlta.speot.isView Answer on Stackoverflow
Solution 5 - MysqlJack DavidsonView Answer on Stackoverflow
Solution 6 - MysqlGrayView Answer on Stackoverflow
Solution 7 - Mysqlvedavyasa kView Answer on Stackoverflow
Solution 8 - Mysqlg10guangView Answer on Stackoverflow
Solution 9 - MysqlAmalgovinusView Answer on Stackoverflow
Solution 10 - Mysqlanjaneyulubatta505View Answer on Stackoverflow
Solution 11 - MysqlAnderson BarrosView Answer on Stackoverflow
Solution 12 - MysqlEdgeCaseBergView Answer on Stackoverflow
Solution 13 - MysqlShiyaoView Answer on Stackoverflow
Solution 14 - MysqlBegueradjView Answer on Stackoverflow
Solution 15 - MysqlDEVView Answer on Stackoverflow
Solution 16 - MysqlHarmView Answer on Stackoverflow
Solution 17 - MysqlFabrício José SouzaView Answer on Stackoverflow
Solution 18 - MysqlnaubeView Answer on Stackoverflow