![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961277.webp)
Creating and Using
InnoDB
Tables
1257
•
InnoDB
permits a foreign key to reference any index column or group of columns. However, in
the referenced table, there must be an index where the referenced columns are listed as the first
columns in the same order.
•
InnoDB
does not currently support foreign keys for tables with user-defined partitioning. This means
that no user-partitioned
InnoDB
table may contain foreign key references or columns referenced by
foreign keys.
•
InnoDB
allows a foreign key constraint to reference a non-unique key. This is an
InnoDB
extension
to standard SQL.
Referential Actions
Referential actions for foreign keys of
InnoDB
tables are subject to the following conditions:
• While
SET DEFAULT
is allowed by the MySQL Server, it is rejected as invalid by
InnoDB
.
CREATE
TABLE
and
ALTER TABLE
statements using this clause are not allowed for InnoDB tables.
• If there are several rows in the parent table that have the same referenced key value,
InnoDB
acts
in foreign key checks as if the other parent rows with the same key value do not exist. For example,
if you have defined a
RESTRICT
type constraint, and there is a child row with several parent rows,
InnoDB
does not permit the deletion of any of those parent rows.
•
InnoDB
performs cascading operations through a depth-first algorithm, based on records in the
indexes corresponding to the foreign key constraints.
• If
ON UPDATE CASCADE
or
ON UPDATE SET NULL
recurses to update the same table it has
previously updated during the cascade, it acts like
RESTRICT
. This means that you cannot use self-
referential
ON UPDATE CASCADE
or
ON UPDATE SET NULL
operations. This is to prevent infinite
loops resulting from cascaded updates. A self-referential
ON DELETE SET NULL
, on the other hand,
is possible, as is a self-referential
ON DELETE CASCADE
. Cascading operations may not be nested
more than 15 levels deep.
• Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows,
InnoDB
checks
UNIQUE
and
FOREIGN KEY
constraints row-by-row. When performing foreign key checks,
InnoDB
sets shared row-level locks on child or parent records it has to look at.
InnoDB
checks
foreign key constraints immediately; the check is not deferred to transaction commit. According to
the SQL standard, the default behavior should be deferred checking. That is, constraints are only
checked after the entire SQL statement has been processed. Until
InnoDB
implements deferred
constraint checking, some things will be impossible, such as deleting a record that refers to itself
using a foreign key.
Foreign Key Usage and Error Information
You can obtain general information about foreign keys and their usage from querying the
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
table, and more information more specific to
InnoDB
tables can be found in the
INNODB_SYS_FOREIGN
and
INNODB_SYS_FOREIGN_COLS
tables, also in
the
INFORMATION_SCHEMA
database. See also
Section 13.1.10.2, “Using
FOREIGN KEY
Constraints”
.
In addition to
SHOW ERRORS
, in the event of a foreign key error involving
InnoDB
tables (usually Error
150 in the MySQL Server), you can obtain a detailed explanation of the most recent
InnoDB
foreign
key error by checking the output of
SHOW ENGINE INNODB STATUS
.
14.2.3.5.
InnoDB
and MySQL Replication
MySQL replication works for
InnoDB
tables as it does for
MyISAM
tables. It is also possible to use
replication in a way where the storage engine on the slave is not the same as the original storage
engine on the master. For example, you can replicate modifications to an
InnoDB
table on the master
to a
MyISAM
table on the slave.
To set up a new slave for a master, you have to make a copy of the
InnoDB
tablespace and the
log files, as well as the
.frm
files of the
InnoDB
tables, and move the copies to the slave. If the
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...