(mysql.info.gz) InnoDB foreign key constraints

Info Catalog

(mysql.info.gz) InnoDB auto-increment column

(mysql.info.gz) Using InnoDB tables

(mysql.info.gz) InnoDB and MySQL Replication

 
 15.7.4 `FOREIGN KEY' Constraints
 --------------------------------
 
 Starting from MySQL 3.23.44, `InnoDB' features foreign key constraints.
 
 The syntax of 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 | SET DEFAULT}]
          [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
 
 Both tables must be `InnoDB' type.  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 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.
 
 `InnoDB' needs indexes on foreign keys and referenced keys so that
 foreign key checks can be fast and not require a table scan.  Starting
 with MySQL 4.1.2, these indexes are created automatically.  In older
 versions, the indexes must be created explicitly or the creation of
 foreign key constraints will 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 the signedness of
 integer types has to 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 string refers to errno 150, this means that the
 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.  Starting from MySQL 4.0.13, you can use
 `SHOW INNODB STATUS' to display a detailed explanation of the latest
 `InnoDB' foreign key error in the server.
 
 Starting from MySQL 3.23.50, `InnoDB' does not check foreign key
 constraints on those foreign key or referenced key values that contain
 a `NULL' column.
 
 *A deviation from SQL standards:* If in the parent table there are
 several rows that have the same referenced key value, then `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.
 
 Starting from MySQL 3.23.50, you can also associate the `ON DELETE
 CASCADE' or `ON DELETE SET NULL' clause with the foreign key
 constraint. Corresponding `ON UPDATE' options are available starting
 from 4.0.8. If `ON DELETE CASCADE' is specified, and a row in the
 parent table is deleted, `InnoDB' automatically deletes also all those
 rows in the child table whose foreign key values are equal to the
 referenced key value in the parent row. If `ON DELETE SET NULL' is
 specified, the child rows are automatically updated so that the columns
 in the foreign key are set to the SQL `NULL' value.  `SET DEFAULT' is
 parsed but ignored by MySQL.  `InnoDB' will reject table definitions
 containing `ON DELETE SET DEFAULT' clauses.
 
 `InnoDB' performs cascading operations through a depth-first algorithm,
 based on records in the indexes corresponding to the foreign key
 constraints.
 
 *A 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 from 4.0.13.  A self-referential `ON DELETE CASCADE' has
 been possible since `ON DELETE' was implemented.  Since 4.0.21,
 cascading operations may not be nested more than 15 levels.
 
 *A 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 that constraints are only
 checked after the WHOLE SQL statement has been processed.
 
 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)
      ) TYPE=INNODB;
      CREATE TABLE child(id INT, parent_id INT,
                         INDEX par_ind (parent_id),
                         FOREIGN KEY (parent_id) REFERENCES parent(id)
                           ON DELETE CASCADE
      ) TYPE=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)) TYPE=INNODB;
      CREATE TABLE customer (id INT NOT NULL,
                            PRIMARY KEY (id)) TYPE=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)) TYPE=INNODB;
 
 Starting from MySQL 3.23.50, `InnoDB' allows you to add a new foreign
 key constraint to a table by using `ALTER TABLE':
 
      ALTER TABLE yourtablename
          ADD [CONSTRAINT SYMBOL] FOREIGN KEY [ID] (INDEX_COL_NAME, ...)
          REFERENCES TBL_NAME (INDEX_COL_NAME, ...)
          [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
          [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
 
 *Remember to create the required indexes first*.  You can also add a
 self-referential foreign key constraint to a table using `ALTER TABLE'.
 
 Starting from MySQL 4.0.13, `InnoDB' supports the use of `ALTER TABLE'
 to drop foreign keys:
 
      ALTER TABLE YOURTABLENAME 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.  (A constraint name can be given as of MySQL 4.0.18.)  Otherwise,
 the `fk_symbol' value is internally generated by `InnoDB' when the
 foreign key is created. To find out the symbol when you want to drop a
 foreign key, use the `SHOW CREATE TABLE' statement.  An 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
      ) TYPE=InnoDB CHARSET=latin1
      1 row in set (0.01 sec)
 
      mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
 
 Starting from MySQL 3.23.50, the `InnoDB' parser allows you to use
 backticks around table and column names in a `FOREIGN KEY ...
 REFERENCES ...' clause.  Starting from MySQL 4.0.5, the `InnoDB' parser
 also takes into account the `lower_case_table_names' system variable
 setting.
 
 Before MySQL 3.23.50, `ALTER TABLE' or `CREATE INDEX' should not be
 used in connection with tables that have foreign key constraints or
 that are referenced in foreign key constraints: Any `ALTER TABLE'
 removes all foreign key constraints defined for the table. You should
 not use `ALTER TABLE' with the referenced table, either. Instead, use
 `DROP TABLE' and `CREATE TABLE' to modify the schema. When MySQL does
 an `ALTER TABLE' it may internally use `RENAME TABLE', and that will
 confuse the foreign key constraints that refer to the table.  In MySQL,
 a `CREATE INDEX' statement is processed as an `ALTER TABLE', so the
 same considerations apply.
 
 Starting from MySQL 3.23.50, `InnoDB' returns the foreign key
 definitions of a table as part of the output of the `SHOW CREATE TABLE'
 statement:
 
      SHOW CREATE TABLE TBL_NAME;
 
 From this version, `mysqldump' also produces correct definitions of
 tables to the dump file, and does not forget about the foreign keys.
 
 You can 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 as of MySQL 4.1.1. This
 avoids problems with tables having to be reloaded in a particular order
 when the dump is reloaded.  For earlier versions, you can disable the
 variable manually within `mysql' when loading the dump file like this:
 
      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.  `FOREIGN_KEY_CHECKS' is available
 starting from MySQL 3.23.52 and 4.0.3.
 
 Setting `FOREIGN_KEY_CHECKS' to 0 can also be useful for ignoring
 foreign key constraints during `LOAD DATA' operations.
 
 `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
 string.
 

Info Catalog

(mysql.info.gz) InnoDB auto-increment column

(mysql.info.gz) Using InnoDB tables

(mysql.info.gz) InnoDB and MySQL Replication