Skip navigation links

Additional languages
13.2.6.5 InnoDB and MySQL Replication »
Section Navigation      [Toggle]

13.2.6.4. FOREIGN KEY Constraints

InnoDB also supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this: 

[CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name.

Foreign keys definitions are subject to the following conditions:

  • Both tables must be InnoDB tables and they must not be TEMPORARY tables.

  • Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.

  • In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. index_name, if given, is used as described previously.

  • In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

  • Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.

  • If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

InnoDB rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. The action InnoDB takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, InnoDB supports five options regarding the action to be taken:

  • CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

    If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

  • NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not allowed to proceed if there is a related foreign key value in the referenced table. InnoDB rejects the delete or update operation for the parent table.

  • RESTRICT: Rejects the delete or update operation for the parent table. NO ACTION and RESTRICT are the same as omitting the ON DELETE or ON UPDATE clause. (Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.)

  • SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

Note that InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The index on the foreign key is created automatically. This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to errno 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Note

InnoDB does not check foreign key constraints on those foreign key or referenced key values that contain a NULL column.

Note

Currently, triggers are not activated by cascaded foreign key actions.

Deviation from SQL standards: 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 allow 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.

Deviation from SQL standards: A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL.

Deviation from SQL standards: 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.

Deviation from SQL standards: 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. 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 will be impossible, such as deleting a record that refers to itself via a foreign key.

Here is a simple example that relates parent and child tables through a single-column foreign key: 

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table: 

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE: 

ALTER TABLE tbl_name
    ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Remember to create the required indexes first. You can also add a self-referential foreign key constraint to a table using ALTER TABLE.

InnoDB also supports the use of ALTER TABLE to drop foreign keys: 

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the FOREIGN KEY clause included a CONSTRAINT name when you created the foreign key, you can refer to that name to drop the foreign key. Otherwise, the fk_symbol value is internally generated by InnoDB when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use the SHOW CREATE TABLE statement. For example: 

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. Separate statements are required.

If ALTER TABLE for an InnoDB table results in changes to column values (for example, because a column is truncated), InnoDB's FOREIGN KEY constraint checks do not notice possible violations caused by changing the values.

The InnoDB parser allows table and column identifiers in a FOREIGN KEY ... REFERENCES ... clause to be quoted within backticks. (Alternatively, double quotes can be used if the ANSI_QUOTES SQL mode is enabled.) The InnoDB parser also takes into account the setting of the lower_case_table_names system variable.

InnoDB returns a table's foreign key definitions as part of the output of the SHOW CREATE TABLE statement: 

SHOW CREATE TABLE tbl_name;

mysqldump also produces correct definitions of tables to the dump file, and does not forget about the foreign keys.

You can also display the foreign key constraints for a table like this: 

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

The foreign key constraints are listed in the Comment column of the output.

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.

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually: 

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring foreign key constraints during LOAD DATA and ALTER TABLE operations. However, even if FOREIGN_KEY_CHECKS=0, InnoDB does not allow the creation of a foreign key constraint where a column references a non-matching column type. Also, if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first.

InnoDB does not allow you to drop a table that is referenced by a FOREIGN KEY constraint, unless you do SET FOREIGN_KEY_CHECKS=0. When you drop a table, the constraints that were defined in its create statement are also dropped.

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message.


User Comments

Posted by Dennis Haney on July 15 2003 10:26am[Delete] [Edit]

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 11:09pm[Delete] [Edit]

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 12:55pm[Delete] [Edit]

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 Jeremy Miller on February 9 2004 5:49am[Delete] [Edit]

A further deviation from ANSI SQL-92 standards, at the time of writing, is the way MySQL treats the ON DELETE NO ACTION and ON UPDATE NO ACTION clauses, should you attempt to use them.

Under the ANSI SQL-92 standard, NO ACTION means "no action" in the sense that an attempt to delete or update a primary key value will not be allowed to proceed if there is a related foreign key value in the referenced table (Gruber, 2000:181). It is therefore the ANSI syntax for explicitly enforcing referential integrity and is supported by SQL Server 2000, Oracle 9 amongst other database systems.

However, in MySQL it should be noted that NO ACTION cannot be used for this purpose at present. In fact, including an ON DELETE NO ACTION or ON UPDATE NO ACTION clause in a MySQL/InnoDB CREATE TABLE statement *allows* the deletion or update of a primary key value regardless of whether it appears as a foreign key value in the related table.

If standard referential integrity restrcitions are required for InnoDB tables in MySQL, the ON DELETE RESTRICT/ON UPDATE RESTRICT syntax should be used instead. Or, alternatively, you can omit this clause entirely and the restriction will be applied as it is the default behaviour for a FOREIGN KEY...REFERENCES clause in a MySQL InnoDB table.

This point was confirmed recently by Victoria Reznichenko on the MySQL General Discussion List (http://lists.mysql.com/mysql/158543), following a long thread on the subject in 2003 (http://lists.mysql.com/mysql/134850).

According to Victoria, this discrepancy will be addressed in the future, so that NO ACTION becomes a synonym for RESTRICT and acts in the ANSI standard way. In the meantime, however, other users may find it useful to be aware of this discrepancy.

Posted by Singer Wang on June 8 2004 10:07pm[Delete] [Edit]

<quote>
InnoDB allows you to drop any table, even though that would break the foreign key constraints that reference the table. When you drop a table, the constraints that were defined in its create statement are also dropped.
</quote>

is not true for 4.0.X, in my 4.0.20 installation it will refuse to drop the table. In my 3.23.58 (with InnoDB), it will drop the table fine..

Posted by Andrew Penry on July 7 2004 2:35pm[Delete] [Edit]

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 8:10am[Delete] [Edit]

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 Dave Howorth on August 11 2004 12:09pm[Delete] [Edit]

Singer Wang wrote:
> <quote>
> InnoDB allows you to drop any table, even though that would break the foreign key constraints that reference the table. When you drop a table, the constraints that were defined in its create statement are also dropped.
> </quote>
>
> is not true for 4.0.X, in my 4.0.20 installation it will refuse to drop the table. In my 3.23.58 (with InnoDB), it will drop the table fine..

I saw the same problem, but I overcame it by using

SET FOREIGN_KEY_CHECKS = 0;

before using DROP TABLE. Afterwards I used:

SET FOREIGN_KEY_CHECKS = 1;

Posted by Fabio Venuti on September 20 2004 1:32pm[Delete] [Edit]

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 Peter Wilson on February 4 2005 10:51pm[Delete] [Edit]

If you can't figure out why a foreign key constraint is failing, try: SHOW INNODB STATUS

It has a section for the last foreign key constraint that failed on an InnoDB table.

(Yes, this is listed in the manual text, but not very prominently. )

Posted by Nicole King on August 26 2005 2:59pm[Delete] [Edit]

You'll also get 1005/150 error if local and referenced fields have different collating sequences.

Posted by Joao S. O. Bueno Calligaris on October 20 2005 12:42pm[Delete] [Edit]

Note that the result of 'CREATE TABLE % LIKE % ' will _not_ have any Foreign Keys that are on the original table (MySQL 5.0.13).

Posted by Randy Kaelber on April 24 2006 2:32pm[Delete] [Edit]

Here's another gotcha to avoid the dread 150 error. If you declare an int field without a length (it defaults to 11) it cannot be used as a foreign key constraint. You should explicitly declare them as int(11), which is a sound policy anyway, but sometimes when you're testing things you just want it to work. I had this gotcha in 4.1.14, so I don't know if it applies to 5 or not, but if you get a 150, it's worth checking to see if that's an issue.

Posted by Bryce Nesbitt on July 11 2006 3:37am[Delete] [Edit]

Warning: mysql silenty ignores forign key requests, on table tables that don't support it. For example on an MyISAM table:

alter table one add FOREIGN KEY (reftest) REFERENCES junkjunk (thisdoesnotexist)
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

Posted by [name withheld] on September 4 2006 1:58am[Delete] [Edit]

[quote]Posted by Ian Rothmann on March 24 2006 7:31am [Delete] [Edit]

I just want to state it again, it's not that clear in the post above: Error number 1005 with reference to error 150 and CHAR/VARCHAR foreign keys. Check your COLLATION. If the one field is UTF and the other LATIN or whatever, your foreign key won't work.

phpMyAdmin gives you the error "No Index defined (fieldname)".

SHOW ENGINE INNODB tells you "Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint"[/quote]

Thanks! I was having a similiar problem, but you put me on the right track. Mine wasn't a collation problem, but the fact that the field I was trying to reference was int(10) and the field referencing it was a smallint(5).

And yeah, the no index defined problem wasn't making any sense.

Posted by matt emmons on September 4 2006 7:56pm[Delete] [Edit]

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 8:16pm[Delete] [Edit]

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 Bryce Nesbitt on March 19 2007 8:27pm[Delete] [Edit]

Note that BOTH the referencing and referenced tables must have an index.

This can be quite wasteful in the case of simple labels. For example imagine a million row table that references a 5 row "status type" table. Even though the 5 row status type table will never change, the million row index will be kept updated.

Posted by Dennis Nikolaenko on June 5 2007 12:29pm[Delete] [Edit]

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 8:34pm[Delete] [Edit]

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 Andre Stechert on August 23 2007 6:31pm[Delete] [Edit]

If you run into error number 1005/errno 150, one other possibility to consider is that the column that's being referenced by the foreign key is not in an innodb table. In practice, this happened to us because we were using an object relational mapper that created association tables for us, but wasn't smart enough to notice that the mysql engine type for the entities being associated had been set to innodb. Thus the entities were innodb tables and the association table was myisam. The error message from 'show innodb status' was somewhat unhelpful in that it essentially said "couldn't find this table" even though the table clearly existed via 'show tables'. Even more confusingly, using an alternate syntax for the creation of the table with the foreign key reference worked.

Posted by Alex Baeza on March 24 2008 7:15pm[Delete] [Edit]

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 Elinor Hurst on April 9 2008 8:58am[Delete] [Edit]

On trying to add a foreign key constraint to a table I was getting the following SQL error:

[Error Code: 1005, SQL State: HY000] Can't create table (errno: 150)

Using the mysql command line utility, I checked the status using
mysql> show engine innodb status\G
(as stated here) which came up with the following information:

errno 150 cannot resolve table name close to

My original query was:
alter table user add foreign key (job_id) references jobs.id

To make it work, replace the dot in jobs.id with the correct syntax (using parentheses).

Correct query:
alter table user add foreign key (job_id) references jobs(id)

What a silly mistake to make! I am hoping I might not be the only one though and this might bring comfort to others :-p

Add your own comment.