InnoDB
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
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_option
]reference_option
: 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 nonbinary (character)
string columns, the character set and collation must be the
same.
InnoDB
requires indexes on foreign keys
and referenced keys so that foreign key checks can be fast
and not require a table scan. 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. (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.) index_name
, if given, is
used as described previously.
InnoDB
allows 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.
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
clause is given,
the symbol
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. If ON DELETE
or
ON UPDATE
are not specified, the default
action is RESTRICT
.
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.
Currently, cascaded foreign key actions to not activate triggers.
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. Specifying
RESTRICT
(or NO
ACTION
) is 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
is the same as
RESTRICT
.)
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.
InnoDB
supports foreign key references within
a table. In these cases, “child table records”
really refers to dependent records within the same table.
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 TABLEtbl_name
ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_option
]
The foreign key can be self referential (referring to the same
table). When you add a foreign key constraint to a table using
ALTER TABLE
, remember
to create the required indexes first.
InnoDB
supports the use of
ALTER TABLE
to drop foreign keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_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 in 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 FROMdb_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
mysql>dump_file_name
;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 nonmatching 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 error 150 in the error message.
If MySQL reports an error number 1005 from a
CREATE TABLE
statement, and the
error message refers to error 150, table creation failed because
a foreign key constraint was not correctly formed. Similarly, if
an ALTER TABLE
fails and it
refers to error 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.
For users familiar with the ANSI/ISO SQL Standard, please note
that no storage engine, including InnoDB
,
recognizes or enforces the MATCH
clause
used in referential-integrity constraint definitions. Use of
an explicit MATCH
clause will not have the
specified effect, and also causes ON DELETE
and ON UPDATE
clauses to be ignored. For
these reasons, specifying MATCH
should be
avoided.
The MATCH
clause in the SQL standard
controls how NULL
values in a composite
(multiple-column) foreign key are handled when comparing to a
primary key. InnoDB
essentially implements
the semantics defined by MATCH SIMPLE
,
which allow a foreign key to be all or partially
NULL
. In that case, the (child table) row
containing such a foreign key is allowed to be inserted, and
does not match any row in the referenced (parent) table. It is
possible to implement other semantics using triggers.
Additionally, MySQL and InnoDB
require that
the referenced columns be indexed for performance. However,
the system does not enforce a requirement that the referenced
columns be UNIQUE
or be declared
NOT NULL
. The handling of foreign key
references to nonunique keys or keys that contain
NULL
values is not well defined for
operations such as UPDATE
or
DELETE CASCADE
. You are advised to use
foreign keys that reference only UNIQUE
and
NOT NULL
keys.
Furthermore, InnoDB
does not recognize or
support “inline REFERENCES
specifications” (as defined in the SQL standard) where
the references are defined as part of the column
specification. InnoDB
accepts
REFERENCES
clauses only when specified as
part of a separate FOREIGN KEY
specification. For other storage engines, MySQL Server parses
and ignores foreign key specifications.
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.
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/
Add your own comment.