mysql forige key

Love in coding...

    Free and Susan

[引]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 be TEMPORARY 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 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 UPDATEoperation that attempts to create a foreign key value in a child tableif there is no a matching candidate key value in the parent table. Theaction InnoDB takes for any UPDATE or DELETEoperation that attempts to update or delete a candidate key value inthe parent table that has some matching rows in the child table isdependent on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEYclause. When the user attempts to delete or update a row from a parenttable, 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.

• NO ACTION: In standard SQL, NO ACTION means no actionin the sense that an attempt to delete or update a primary key value isnot allowed to proceed if there is a related foreign key value in thereferenced 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.

InnoDBrequires indexes on foreign keys and referenced keys so that foreignkey checks can be fast and not require a table scan. The index on theforeign key is created automatically. This is in contrast to some olderversions, in which indexes had to be created explicitly or the creationof 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 TABLEstatement, and the error message refers to errno 150, table creationfailed because a foreign key constraint was not correctly formed.Similarly, if an ALTER TABLE fails and itrefers to errno 150, that means a foreign key definition would beincorrectly 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, InnoDBacts in foreign key checks as if the other parent rows with the samekey 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.

InnoDBperforms cascading operations through a depth-first algorithm, based onrecords 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 KEYconstraints row-by-row. According to the SQL standard, the defaultbehavior should be deferred checking. That is, constraints are onlychecked after the entire SQL statement has been processed. Until InnoDBimplements deferred constraint checking, some things will beimpossible, such as deleting a record that refers to itself via aforeign key.