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 gt  select   from department     - gt     ------------ ---------- -----------    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 gt  show columns from department     - gt     ----------- --------------- ------ ----- --------- -------    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 gt  create table course     - gt   course id varchar 7       - gt  title varchar  50       - gt  dept name varchar 20       - gt  credits numeric 2 0       - gt  primary key course id       - gt  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 gt  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 gt  set foreign key checks 0     - gt    Query OK  0 rows affected  0 00 sec  mysql gt  create table course     - gt   course id varchar 7       - gt  title varchar 50       - gt  dept name varchar 20       - gt  credits numeric 2 0       - gt  primary key course id       - gt  foreign key  dept name  references department   ERROR 1215  HY000   Cannot add foreign key constraint

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  create table course - gt   course id varchar 7   - gt  title varchar  50   - gt  dept name varchar 20   - gt  credits numeric 2 0   - gt  primary key course id   - gt  foreign key  dept name  references department  ENGINE INNODB

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Just add  unsigned  for the FOREIGN constraint   FK  int 11  unsigned DEFAULT NULL

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

Examples related to mysql

Examples related to sql

Examples related to database