[引]MySQL INNODB类型表的外键关联设置
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
also supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB
looks like this:
[CONSTRAINT symbol
] FOREIGN KEY [id
] (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}]
Foreign keys definitions are subject to the following conditions:
-
-
Both tables must be
InnoDB
tables and they must not beTEMPORARY
tables. -
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.
-
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
andTEXT
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, thesymbol
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. BothON DELETE CASCADE
andON UPDATE CASCADE
are supported. Between two tables, you should not define severalON 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 toNULL
. This is valid only if the foreign key columns do not have theNOT NULL
qualifier specified. BothON DELETE SET NULL
andON UPDATE SET NULL
clauses are supported. -
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
andRESTRICT
are the same as omitting theON DELETE
orON UPDATE
clause. (Some database systems have deferred checks, andNO ACTION
is a deferred check. In MySQL, foreign key constraints are checked immediately, soNO ACTION
andRESTRICT
are the same.) -
SET DEFAULT
: This action is recognized by the parser, butInnoDB
rejects table definitions containingON DELETE SET DEFAULT
orON 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.
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. If you specify a SET NULL
action, make sure that you have not declared the columns in the child table as NOT NULL
.
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.
You cannot create a table with a column name that matches the name of an internal InnoDB column (including DB_ROW_ID
, DB_TRX_ID
, DB_ROLL_PTR
and DB_MIX_ID
). In versions of MySQL before 5.1.10 this would cause a crash, since 5.1.10 the server will report error 1005 and refers to errno
-1 in the error message.
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.