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 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. 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 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
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.
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.
分享到:
相关推荐
今儿继续再看老师给推荐的深入浅出mysql数据库开发这本书,看到innodb数据库的外键关联问题时,遇到了一个问题,书上写的是可以对父表进行修改,从而同步到子表的外键上去,可是自己的实验却是没有能够。 代码如下:...
两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持); 2.外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示...
(1)只有InnoDB类型的表才可以使用外键,mysql默认是MyISAM,这种类型不支持外键约束 (2)外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作; (3)外键的作用: 保持数据一致性,完整性,...
关联2张表时出现了无法创建外键的情况,从这个博客看到,问题出在第六点的Charset和Collate选项在表级和字段级上的一致性上。我的2张表的编码charset和collate不一致,2张表都执行执行SQL语句: alter table 表名 ...
在MySQL中,InnoDB引擎类型的表支持了外键约束。外键的使用条件:1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持);2.外键列必须建立了索引,MySQL 4.1.2以后的版本...
在MySQL中,InnoDB引擎类型的表支持了外键约束。 外键的使用条件: 1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持); 2.外键列必须建立了索引,MySQL 4.1.2以后的...
MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括...
6.8.1使用MySQL构建一个队列表251 6.8.2计算两点之间的距离254 6.8.3使用用户自定义函数257 6.9总结258 第7章MySQL高级特性259 7.1分区表259 7.1.1分区表的原理260 7.1.2分区表的类型261 7.1.3如何使用分区...
针对上面创建的两个表, 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的, 那么在主表删除记录的时候, 如果子表有对应记录, 则不允许删除, 主表在更新记录的时候, 如果子表有对应记录, 则子表...
答:MySQL的存储引擎是底层管理MySQL表的程序库,它决定了如何存储、检索和更新表中的数据。MyISAM和InnoDB是两种MySQL的常见存储引擎。两个引擎的主要区别如下: MyISAM:不支持事务和行级锁定,支持全文索引和...
4>.InnoDB支持外键,而MyISAM不支持 5>.InnoDB不支持全文索引,而MyISAM支持。 (2)、innodb引擎的4大特性 插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead) (3)、2者select...
4>.InnoDB 支持外键,而 MyISAM 不支持 5>.InnoDB 不支持全文索引,而 MyISAM 支持。 (2)、innodb 引擎的 4 大特性 插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead) (3)、
14.8 InnoDB数据表的管理 14.9 MySQL服务器的优化 14.10 ISP数据库管理 第四部分 程序设计 第15章 PHP 15.1 mysql功能模块 15.2 mysqli的类、方法和属性 15.3 把数据库功能打包为一个类 ...
MySQL基于关系型数据库模型,数据以表格形式组织,并通过预定义的键(如主键、外键)在表之间建立关联。它完全支持结构化查询语言(SQL),允许用户进行数据查询、插入、更新、删除、创建和管理数据库结构等操作。...
14.8 InnoDB数据表的管理 14.9 MySQL服务器的优化 14.10 ISP数据库管理 第四部分 程序设计 第15章 PHP 15.1 mysql功能模块 15.2 mysqli的类、方法和属性 15.3 把数据库功能打包为一个类 15.4 把...
MySQL基于关系型数据库模型,数据以表格形式组织,并通过预定义的键(如主键、外键)在表之间建立关联。它完全支持结构化查询语言(SQL),允许用户进行数据查询、插入、更新、删除、创建和管理数据库结构等操作。...
MySQL基于关系型数据库模型,数据以表格形式组织,并通过预定义的键(如主键、外键)在表之间建立关联。它完全支持结构化查询语言(SQL),允许用户进行数据查询、插入、更新、删除、创建和管理数据库结构等操作。...
MySQL基于关系型数据库模型,数据以表格形式组织,并通过预定义的键(如主键、外键)在表之间建立关联。它完全支持结构化查询语言(SQL),允许用户进行数据查询、插入、更新、删除、创建和管理数据库结构等操作。...
MySQL基于关系型数据库模型,数据以表格形式组织,并通过预定义的键(如主键、外键)在表之间建立关联。它完全支持结构化查询语言(SQL),允许用户进行数据查询、插入、更新、删除、创建和管理数据库结构等操作。...