MySQL :: MySQL 5.6 Reference Manual :: 14.8.7 InnoDB and FOREIGN KEY Constraints

MySQL 5.6 Reference Manual / ... / InnoDB and FOREIGN KEY Constraints

14.8.7 InnoDB and FOREIGN KEY Constraints

This section describes differences in the InnoDB storage engine's handling of foreign keys as compared with that of the MySQL Server.

For foreign key usage information and examples, see Section 13.1.17.5, “Using FOREIGN KEY Constraints”.

Foreign Key Definitions

Foreign key definitions for InnoDB tables are subject to the following conditions:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Referential Actions

Referential actions for foreign keys of InnoDB tables are subject to the following conditions:

While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.
If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not permit the deletion of any of those parent rows.
InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.
If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.
Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

Foreign Key Usage and Error Information

You can obtain general information about foreign keys and their usage from querying the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table, and more information more specific to InnoDB tables can be found in the INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS tables, also in the INFORMATION_SCHEMA database.

In addition to SHOW ERRORS, in the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.

PREV HOME UP NEXT

User Comments

Posted by Dennis Haney on July 15, 2003

For those encountering the problem " ERROR 1216: Cannot add or update a child row: a foreign key constraint fails", it actually means what it says! Some row in the child does not comply with the constraint, correct the problem.
You find the rows like this:
select child.id from child left join parent on (child.parent_id=parent.id) where child.id is not null and parent.id is null;

Posted by Kai Baku on October 10, 2003

There may be rare cases where circular dependencies would make sense. In the case of employees and store, you may have a circular dependency; in which all employees must be stationed at a store, so the employees table will have storeID and EmployeeID attached as a concatonated primary key (Presuming that an Employee can only have one store stationed) or even just a simple non-dependent foriegn key. Then each store must have a top general manager in charge which is stationed there, so the store will have an EmployeeID Foreign Key to the store table to represent that the store has that employee as the manager.

In this case, you have StoreID as an attribute of Employee, and EmployeeID (the Manager) as an attribute of Store.

While this works, it may not be the best method. There are likely better ways to handle such cases, but if your business rules requires such circular dependencies, then it happens.

Also, it is interesting to note that while this query works (Note the PRIMARY KEY line):

CREATE TABLE `ffxi_characterJob` (
`serverID` int(11) NOT NULL,
`userid` int(10)unsigned NOT NULL,
`characterName` varchar(255) NOT NULL,
`jobAbbr` char(4) NOT NULL,
`jobLevel` int(11) default '0',
PRIMARY KEY (`serverID`,`userid`,`characterName`,`jobAbbr`),
INDEX (`jobAbbr`),
CONSTRAINT FOREIGN KEY (`serverID`,`userid`,`characterName`) REFERENCES `ffxi_characters` (`serverID`,`userid`,`characterName`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (`jobAbbr`) REFERENCES `ffxi_jobType` (`jobAbbr`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

This query will give you an error 1005 and errno 150:

CREATE TABLE `ffxi_characterJob` (
`serverID` int(11) NOT NULL,
`userid` int(10)unsigned NOT NULL,
`characterName` varchar(255) NOT NULL,
`jobAbbr` char(4) NOT NULL,
`jobLevel` int(11) default '0',
PRIMARY KEY (`jobAbbr`,`serverID`,`userid`,`characterName`),
INDEX (`jobAbbr`),
CONSTRAINT FOREIGN KEY (`serverID`,`userid`,`characterName`) REFERENCES `ffxi_characters` (`serverID`,`userid`,`characterName`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (`jobAbbr`) REFERENCES `ffxi_jobType` (`jobAbbr`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

In order to make the second one work, you have to add:
INDEX (`serverID`,`userid`,`characterName`)
before the the foreign key is made.

Posted by Turadg Aleahmad on January 7, 2004

In a previous comment Dennis Haney provided an SQL snippet for finding rows that violate intended foreign key constraints. I had a lot of data to check so I write a little shell script to save me time:

--

#!/bin/sh
# find-fk-conflicts.sh
# (c) 2004 Turadg Aleahmad, licensed under GPL
# USAGE: find-fk-conflict.sh child_table child_key parent_table parent_key

# NOTE: set this
db="TARGET DATABASE"

child_table=$1
child_key=$2
parent_table=$3
parent_key=$4

query="SELECT $child_table.$child_key FROM $child_table LEFT JOIN $parent_table
ON ( $child_table.$child_key = $parent_table.$parent_key)
WHERE $child_table.$child_key IS NOT NULL AND $parent_table.$parent_key IS NULL;
"

mysql --verbose -u root -e "$query" $db

Posted by Andrew Penry on July 7, 2004

If you have a join on part of a primary key, foriegn key constraints may behave in an unexpected way.

CREATE TABLE doc (
docID INTEGER NOT NULL AUTO_INCREMENT,
langCode CHAR(2) NOT NULL,
title VARCHAR(32),
PRIMARY KEY (docID, langCode)
) Type=InnoDB;

CREATE TABLE author (
authorID INTEGER NOT NULL AUTO_INCREMENT,
docID CHAR(2) NOT NULL,
name VARCHAR(32),
PRIMARY KEY (authorID),
FOREIGN KEY (docID) REFERENCES doc(docID) ON DELETE CASCADE ON UPDATE CASCADE
) Type=InnoDB;

In this case you have documents in several languages. The primary key of the document is the docID and the langCode for that translation. The author of the document is only dependant on the docID, not the language of a particular translation. Therefore the FOREIGN KEY is only on docID.

Although this makes sense, the restraint acts a little funny. Say you have the following data:
doc table
docID langCode title
1 hu A Szamitogep
1 en The Computer

author table
authorID docID name
7 1 Kaposzta Csaba

Deleteing any version of the document will delete the entry in the author table. For example:
DELETE FROM doc WHERE docid=1 AND langCode=en;

now the tables look like:

doc table
docID langCode title
1 hu A Szamitogep

author table
authorID docID name

As you can see, deleting just the translation has deleted the author.

I am unsure about whether this is correct behavior. I've tried this using MS Access to compare, and it won't let me buid relationships on partial primary keys. My feeling is that InnoDB should probably not allow me either, because docID is clearly not a unique index.

Posted by Frank Vanderhallen on August 4, 2004

I've too much tables to execute the foreign key dependency checking script by hand. This little script does it all:

#!/bin/sh

# check_constraints.sh
# --------------------
# Check foreign key contraints on MySQL database.
#
# Written by Frank Vanderhallen, licensed under GPL.

if [ -z "$1" ]
then
echo "\nUsage:\n\t./`uname $0` <database> [-h <host>] [-u user] [-p <passwd>]\n"
exit
fi

CONSTRAINTS=`mysqldump $* | grep "CREATE\|CONSTRAINT" | sed 's/ /+/g'`

for c in $CONSTRAINTS
do
if [ "`echo $c | cut -d '+' -f 3`" = "CONSTRAINT" ]
then
CONSTRAINT=`echo $c | cut -d '+' -f 4 | tr -d '\`'`
CHILD_KEY=`echo $c | cut -d '+' -f 7 | tr -d '()\`,'`
PARENT_TABLE=`echo $c | cut -d '+' -f 9 | tr -d '\`'`
PARENT_KEY=`echo $c | cut -d '+' -f 10 | tr -d '()\`,'`
QUERY="select c.$CHILD_KEY from $CHILD_TABLE as c left join $PARENT_TABLE as p on p.$PARENT_KEY=c.$CHILD_KEY where c.$CHILD_KEY is not null and p.$PARENT_KEY is null;"
echo "Checking table '$CHILD_TABLE' constraint '$CONSTRAINT'"
mysql -verbose $* -e "$QUERY"
else
CHILD_TABLE=`echo $c | cut -d '+' -f 3`
fi
done

Posted by Fabio Venuti on September 20, 2004

The fact that "NO ACTION" and "RESTRICT" should be treated equally means that there is no way to delete a parent row without deleting the child row unless you disable the foreign key check. This is normally what we want, but there might be exceptions where it makes sense to keep an orphan row, e.g., when you have a "history" table that maintains some information about records that can be safely deleted.

Posted by matt emmons on September 4, 2006

In 4.1.18 this;

CREATE TABLE foo (
a int(11) NOT NULL default '0',
b int(11) NOT NULL default '0',
PRIMARY KEY (a,b),
KEY b (b),
CONSTRAINT FOREIGN KEY (a) REFERENCES other_table1.a,
CONSTRAINT FOREIGN KEY (b) REFERENCES other_table2.b
) ENGINE=InnoDB;

provokes errno 150 / 1005. Show InnoDB status' latest foreign key error reports, "cannot resolve table name close to...."

All tables (foo, other_table1 & other_table2) are InnoDB. other_table1.a and other_table2.b are single attribute primary keys (thus satisfying the "first column" index requirement).

This, on the other hand, works fine:

CREATE TABLE foo (
a int(11) NOT NULL default '0',
b int(11) NOT NULL default '0',
PRIMARY KEY (a,b),
KEY b (b),
CONSTRAINT FOREIGN KEY (a) REFERENCES other_table1(a),
CONSTRAINT FOREIGN KEY (b) REFERENCES other_table2(b)
) ENGINE=InnoDB;

The only difference is how the referenced field is specified - table.field v. table(field).

I wonder if the indexes, in either the referring or referenced tables, being named the same as their respective fields isn't a problem.

Posted by jim kraai on January 4, 2007

Dynamic Stored Procedure to identify conflicts prior to adding a FOREIGN KEY constraint

Here's a stored proc inspired by by Turadg Aleahmad's shell script named find-fk-conflicts.sh seen in the refman/5.0 page comments:


<?
DELIMITER |

DROP PROCEDURE IF EXISTS sp_find_fk_conflict |

CREATE PROCEDURE         sp_find_fk_conflict(
  IN dbname CHAR(64),    -- database name
  IN ctn CHAR(64),       -- child table name
  IN ckn CHAR(64),       -- child key name
  IN ptn CHAR(64),       -- parent table name
  IN pkn CHAR(64)        -- parent table name
)

COMMENT
"""
  sp_find_fk_conflict

  Created:  20060913
  By jim kraai (jim NO at SPAM kraai.org)

  Inspired by Turadg Aleahmad's find-fk-conflicts shell script

  Inputs:
    dbname
    child table name
    child key name
    parent table name
    parent key name
  Outputs
    rows that would conflict if adding a foreign key constrant

  USAGE:  call sp_find_fk_conflict('some_db','some_childTable','some_childTable_key','some_parentTable','some_parentTable_key');

"""

BEGIN

  DECLARE s TEXT;

  SET @s = CONCAT(
    'SELECT ',    dbname,'.',ctn,'.',ckn,' ',
    'FROM ',      dbname,'.',ctn,' ',
    'LEFT JOIN ', dbname,'.',ptn,
     ' ON ',
        '( ',     dbname,'.',ctn,'.',ckn,
                  ' = ',
                  dbname,'.',ptn,'.',pkn,
        ') ',
    'WHERE ',
                  dbname,'.',ctn,'.',ckn,' IS NOT NULL ',
     ' AND ',
                  dbname,'.',ptn,'.',pkn,' IS NULL;'
  );

  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END; |

DELIMITER ;
?>

(Ignore the PHP <? ?> around the SP, it's there to preserve formatting)

Posted by Dennis Nikolaenko on June 5, 2007

Note that as of version 5.0.38, InnoDB allows two or more foreign keys on the column, they may reference diffrent tables/columns. It even allows foreign keys with the same definition, but different constraint name.

Posted by Lupus Arctos on July 19, 2007

Modified foreign key dependency checker script posted by Frank Vanderhallen.
This script supports composite keys.

#!/bin/sh

# check_constraints.sh
# --------------------
# Check foreign key contraints on MySQL database.
#
# Written by Frank Vanderhallen and modified by Lupus Arctos, licensed under GPL.

if [ -z "$1" ]
then
echo "\nUsage:\n\t./`uname $0` <database> [-h <host>] [-u user] [-p <passwd>]\n"
exit
fi

CONSTRAINTS=`mysqldump $* | grep "CREATE\|CONSTRAINT" | sed 's/, /,/g' | sed 's/ /+/g'`

for c in $CONSTRAINTS
do
if [ "`echo $c | cut -d '+' -f 3`" = "CONSTRAINT" ]
then

CONSTRAINT=`echo $c | cut -d '+' -f 4 | tr -d '\`'`
CHILD_KEY=`echo $c | cut -d '+' -f 7 | tr -d '()\`'`
PARENT_TABLE=`echo $c | cut -d '+' -f 9 | tr -d '\`'`
PARENT_KEY=`echo $c | cut -d '+' -f 10 | tr -d '()\`'`

declare -a PARENT_KEYS=($(echo $PARENT_KEY|sed 's/,/ /g'))
declare -a CHILD_KEYS=($(echo $CHILD_KEY|sed 's/,/ /g'))

let PARENT_KEYS_LASTIDX=${#PARENT_KEYS[@]}-1
let CHILD_KEYS_LASTIDX=${#CHILD_KEYS[@]}-1

JOINON=
CHILD_TABLE_KEY=
for k in `seq 0 $PARENT_KEYS_LASTIDX`; do
JOINON=`echo $JOINON p.${PARENT_KEYS[k]}=c.${CHILD_KEYS[k]}`
CHILD_TABLE_KEY=`echo $CHILD_TABLE_KEY c.${CHILD_KEYS[k]}`
if [ $k != $PARENT_KEYS_LASTIDX ]; then
JOINON=`echo $JOINON and`
CHILD_TABLE_KEY=`echo $CHILD_TABLE_KEY,`
fi
if [ $k == 0 ]; then
CHILD_WHEN=`echo p.${PARENT_KEYS[k]} is not null`
PARENT_WHEN=`echo c.${CHILD_KEYS[k]} is null`
fi
done

QUERY="select $CHILD_TABLE_KEY from $CHILD_TABLE as c left join $PARENT_TABLE as p on $JOINON where $CHILD_WHEN and $PARENT_WHEN;"
echo "Checking table '$CHILD_TABLE' constraint '$CONSTRAINT'"
#mysql -v $* -e "$QUERY"
mysql $* -e "$QUERY"
else
CHILD_TABLE=`echo $c | cut -d '+' -f 3`
fi
done

Posted by Alex Baeza on March 24, 2008

It is often difficult to determine which tables have children.

One nice new feature of MySQL 5.02 and above is the information_schema. You can use the information_schema to determine dependencies using a query such as:

SELECT
ke.referenced_table_name parent,
ke.table_name child,
ke.constraint_name
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
ORDER BY
ke.referenced_table_name;

This will show all the parent tables that have children in your current database. This example can also be modified to show all parent child relationships across multiple databases.

Posted by Altemir Soares on November 28, 2008

Hi,

This way you can do foreign key checks just from any sql client tool.

SET @child_table='EMPLOYEES';
SET @child_key='DEPARTMENT_ID';
SET @parent_table='DEPARTMENTS';
SET @parent_key='DEPARTMENT_ID';

select CONCAT('SELECT ',@child_table ,'.',@child_key,' FROM ',@child_table,' LEFT JOIN ', @parent_table,
CONCAT('\n ON (',@child_table,'.',@child_key,' = ',@parent_table,'.',@parent_key,')'),
CONCAT('\n ','WHERE ',@child_table,'.',@child_key,' IS NOT NULL AND ',@parent_table,'.',@parent_key,' IS NULL'));

Thanks

Posted by Ran Wei on March 10, 2009

RE: Kai Baku
I believe the reason why your second example does not work is the following line from the Manual:
"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order"

In your first example, your primary keys are specified in the order, (`serverID`,`userid`,`characterName`,`jobAbbr`) which is the same as your foreign key specification.

In the second example, your keys are specified in the order, (`jobAbbr`,`serverID`,`userid`,`characterName`), but your foreign key specification places 'jobAbbr' last.

AFAIK, specifying INDEX (`jobAbbr`) will create a new index for jobAbbr but this one will be at the end, rather than the beginning, which again makes your index creation and foreign key creation match up.

Posted by Toby Thain on May 22, 2009

In our projects, we:
1) maintain separate SQL file per table (nicer version control histories)
2) need automated builds
3) use FK constraints extensively

Instead of disabling checks, which disables meaningful validity checks on the constraints, we use make to find a valid creation order. This also detects circular references. Code here: http://www.telegraphics.com.au/svn/fk/trunk/

Posted by Lee Mallabone on June 1, 2010

If you're seeing errno 150 when trying to alter a table to add a foreign key, you have to check the properties of the *table* as well as the types of the columns being referenced.

For example, if you're trying to create a foreign key constraint between two varchar(30) columns, but one table is using latin1 encoding and the other table is using utf8 encoding, the ALTER will fail with errno 150 and no clear indication of the above.

Posted by Seun Motunmori on June 17, 2010

If you, like myself, also ran into troubles with your FKs on InnoDB engine using version 5.1 of MySQL, then the trouble can be fixed by ensure that your referenced table columns are all indexed (original tables of reference).

Example

Referenced Table 1
-------------------------
CREATE TABLE `study_mode` (
`id_study_mode` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`code` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_study_mode`))
ENGINE = InnoDB;

Referenced Table 2
-------------------------
CREATE TABLE `gender` (
`id_gender` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`code` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_gender`))
ENGINE = InnoDB;

Referencing Table with FKs
----------------------------------
CREATE TABLE `prospect` (
`id_prospect` INT NOT NULL AUTO_INCREMENT,
`f_name` VARCHAR(45) NOT NULL,
`l_name` VARCHAR(45) NOT NULL,
`gender` VARCHAR(45) NOT NULL,
`e_mail` VARCHAR(45) NOT NULL,
`birth_year` YEAR NOT NULL,
`study_mode` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_prospect`),
UNIQUE INDEX (`id_prospect`, `e_mail`),
INDEX (`gender`),
FOREIGN KEY (`gender`)
REFERENCES `gender`(`code`)
ON DELETE CASCADE ON UPDATE CASCADE,
INDEX (`study_mode`),
FOREIGN KEY (`study_mode`)
REFERENCES `mydb`.`study_mode`(`code`)
ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = InnoDB;

Ensure that you indexed both Table 1 and Table 2's referenced columns on Table 1 and Table 2 respectively.
If you don't, the error "#1005 - Can't create table 'prospect' (errno:105)" will be flagged. (Indexing is good practice as it avoids full table-scans!)
Once this is taken care of and the referenced columns are of same data-type, you would have successfully created your desired table with as many FKs as you want on it.

Posted by blaise che on July 14, 2010

To avoid 'errno: 150' when dealing with integer data types, verify that the primary and foreign key columns of interest have the same integer types (size and sign, as indicated above). e.g. if primary key is 'unsigned int' and foreign key is simply 'int', then 'errno: 150' is likely. Took me a while to debug this!

Posted by Yann-Gael GAUTHERON on November 9, 2010

Here's a way to list all foreign keys from a database, using mysqldump and PHP :

public function fetch_foreign_keys() {
$cmd = "mysqldump --no-data --lock-tables=0 -u ".$this->login." -p\"".$this->password."\" -h ".$this->host." -P ".$this->port." \"".$this->database."\" 2>&1";
$result = shell_exec($cmd);
preg_match_all("/CREATE TABLE `(.[^`]*)`(.[^\;]*)\;/",$result,$matches);
foreach ($matches[2] as $k => $match) {
preg_match_all("/CONSTRAINT `(.[^`]*)` FOREIGN KEY $`(.[^`]*)`$ REFERENCES `(.[^`]*)` $`(.[^`]*)`$/",$match,$matchesConstraints);

// On enlève les cases inutiles
array_shift($matchesConstraints); // 1ère
array_shift($matchesConstraints); // 2nde
array_pop($matchesConstraints); // Dernière
foreach ($matchesConstraints[1] as $j => $fk) {
$return[$fk][$matches[1][$k]] = $matchesConstraints[0][$j];
}
}
ksort($return);
return $return;
}

Posted by Christopher Beland on April 11, 2011

Note that it will fail if you try to insert data into a table that has a foreign key constraint where the foreign table is a view.

Posted by David Filmer on June 5, 2011

If you get an error:
<b>ERROR 1216: Cannot add or update a child row: a foreign key constraint fails</b>
it means you have two tables, at least one of which contains data, and you are trying to establish a relationship between keys (such as table1.id = table2.id), but there is data in one or both tables which does not currently meet this condition (often because table1 might contain keys that table2 does not contain and/or vice-versa).
You can't force a relationship onto tables which are not already compliant with the constraint (nor would you want to - if you are trying to do this, you haven't thought it out).

Posted by manish patel on July 12, 2011

========To Remove Foreign Key from child table==========

CREATE TABLE parent (id INT NOT NULL, name varchar(25) not null default '',
PRIMARY KEY `id_name` (id,name)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT, parent_name varchar(25) not null default '',
INDEX par_ind (parent_id),
FOREIGN KEY `id_name` (parent_id, parent_name) REFERENCES parent(id,name)
ON Delete CASCADE
on update CASCADE

) ENGINE=INNODB;

#show create table schema of child table
show create table child;

CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`parent_name` varchar(25) NOT NULL DEFAULT '',
KEY `par_ind` (`parent_id`),
KEY `id_name` (`parent_id`,`parent_name`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `parent_name`) REFERENCES `parent` (`id`, `name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

then use CONSTRAINT `child_ibfk_1` to drop as Foreign key from child table by using below query.

alter table child drop FOREIGN KEY child_ibfk_1;

Posted by Markus Zeller on January 5, 2012

I had the same issue and it could be solved by checking the keys to be *exactly* the same.

In my case I had the parent table with int unsigned not null, and the child had int signed not null.

So the signing was causing the error. I changed the child to be unsigned (just a unwanted mistake on creating the table) and all went fine.

Posted by Philip Flammer on June 18, 2012

When you get errors like the errno 150, and errno 121, and you don't have SUPER privileges to run SHOW ENGINE INNODB STATUS, it can take a long time to debug as a number of people above have discussed. Here is a list of known causes and solutions to various foreign key errors including these:

http://eliacom.com/wpErrNo150.php

Posted by Aurelien Marchand on February 27, 2013

INSERT IGNORE will *still* trip the foreign key constraint. It's a known bug that is to be fixed in a later version.

CREATE TABLE T1 (id1 int, index key(id1));
CREATE TABLE T2(id2 int, foreign key (id2) references T1(id1));

INSERT INTO T1(1); /* OK */
START TRANSACTION;
INSERT IGNORE INTO T2(2); /* MySQL error, foreign key constraint violated, even though it was marked as IGNORE */
INSERT IGNORE INTO T2(1);
COMMIT;

end result:
T1 contains (1)
T2 is empty!