Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


14.6.6 InnoDB と FOREIGN KEY 制約

このセクションでは、InnoDB ストレージエンジンでの外部キー処理と、MySQL サーバーでの処理とを比較したときの相違点について説明します。

外部キーの定義

InnoDB テーブルの外部キー定義は、次のような条件の対象となります。

  • InnoDB では、外部キーが任意のインデックスカラムまたはカラムのグループを参照することが許可されます。ただし、参照されるテーブルには、参照されるカラムが同じ順序で最初のカラムとして一覧表示されているインデックスが存在する必要があります。

  • 現在、InnoDB ではユーザー定義のパーティションを持つテーブルの外部キーがサポートされていません。つまり、ユーザーがパーティション化した InnoDB テーブルには、外部キーで参照される外部キー参照またはカラムが含まれる可能性がありません。

  • InnoDB では、外部キー制約が一意でないキーを参照することが許可されます。これは、標準 SQL の InnoDB 拡張です。

参照アクション

InnoDB テーブルの外部キーに関する参照アクションは、次のような条件の対象となります。

  • SET DEFAULT は、MySQL サーバーで許可されていますが、InnoDB では無効として拒否されます。この句を使用した CREATE TABLE および ALTER TABLE ステートメントは、InnoDB テーブルで許可されていません。

  • 同じ参照キー値を持つ複数の行が親テーブルにある場合、InnoDB は、同じキー値を持つほかの親の行が存在しないかのように、外部キーチェックで動作します。たとえば、RESTRICT 型の制約が定義されていて、複数の親の行を含む子の行が存在する場合は、これらの親の行のいずれかを削除することが InnoDB で許可されません。

  • InnoDB では、外部キー制約に対応するインデックス内のレコードに基づいて、深さ優先アルゴリズムを使用したカスケード操作が実行されます。

  • ON UPDATE CASCADE または ON UPDATE SET NULL は、カスケード中に以前に更新していた同じテーブルを更新するように再帰する場合、RESTRICT と同様に機能します。つまり、自己参照型 ON UPDATE CASCADE または ON UPDATE SET NULL 操作は使用できません。この目的は、カスケード更新で発生する無限ループを回避することです。反対に、自己参照型 ON DELETE SET NULL は、自己参照型 ON DELETE CASCADE と同様に動作できます。カスケード操作は、15 レベルよりも深くネストされる可能性がありません。

  • 一般的な MySQL と同様に、多数の行を挿入、削除、または更新する SQL ステートメントでは、InnoDB によって UNIQUE および FOREIGN KEY 制約が 1 行ずつチェックされます。外部キーチェックの実行時に、InnoDB は、調査対象の子または親のレコード上に共有の行レベルロックを設定します。InnoDB では、即座に外部キー制約がチェックされ、そのチェックはトランザクションのコミットまで遅延されません。SQL 標準によると、デフォルトの動作は遅延チェックにするべきです。つまり、SQL ステートメント全体が処理されたあとにはじめて、制約がチェックされます。InnoDB で制約の遅延チェックが実装されるまで、外部キーを使用してそれ自体を参照するレコードを削除するなどの一部の操作が実行できません。

外部キーの使用法とエラー情報

外部キーおよびそれらの使用法に関する一般的な情報は、INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルでクエリーを実行することで取得できます。InnoDB テーブルに固有の詳細な情報は、INNODB_SYS_FOREIGN および INNODB_SYS_FOREIGN_COLS テーブル、または INFORMATION_SCHEMA データベースで見つかります。セクション13.1.17.2「外部キー制約の使用」も参照してください。

SHOW ERRORS 以外でも、InnoDB テーブルが関与する外部キーエラー (通常、MySQL サーバーではエラー 150) の発生時に、SHOW ENGINE INNODB STATUS の出力をチェックすることで、最近の InnoDB 外部キーエラーの詳細な説明を取得できます。


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 @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!
Sign Up Login You must be logged in to post a comment.