User Comments

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;

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.

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

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.

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

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.

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.

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)

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.

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

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.

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

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.

I have recently written a (german) article on how to import InnoDB tables and prevent the error "Cannot delete or update a parent row: a foreign key constraint fails".

You can find it here:
http://www.lunar.lu/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails/

Regards,

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/

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.

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.

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!

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;
}

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.

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

========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;

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.

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

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!

Add your own comment.