heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
通过 APIs (比如 PHP, Perl DBI/DBD, JDBC, ODBC, 或 MySQL 的标准 C 调用接口)，发送一个事务控制语句(比如 "COMMIT" )到 MySQL 服务器可以如同其它的 SQL 语句。例如："SELECT..." 或 "INSERT..."。 APIs often contain separate special commit-transaction methods, 但是 MySQL 对事务的支持还相对比较年轻，他们并不是在所有版本的 APIs 上均能工作的。
如果 CREATE TABLE 给出 1005 号错误，错误信息字符串提示错误号(errno) 150，那么就是因为外键约束未被正确建立而导致表创建失败。同样的，如果一条 ALTER TABLE 失败而返回错误号 150，那就意味着 altered table 未能正确定义一个外键。从 4.0.13 开始，你可以通过使用 SHOW INNODB STATUS 来查看服务器是最后一条 InnoDB 的外键错误的详细说明。
从 3.23.50 开始，InnoDB 不再在允许 NULL 值外键或被引用键上检查外键约束。
与 SQL 标准不一致： if in the parent table there are several rows which have the same referenced key value, then InnoDB acts in foreign key checks like the other parent rows with the same key value would 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.
从 3.23.50 开始，可能联合 ON DELETE CASCADE 或 ON DELETE SET NULL 子句与外键约束一同作用。相应的 ON UPDATE 选项将从 4.0.8 开始支持。如果 ON DELETE CASCADE 被指定，当主表中的记录行被删除时，InnoDB 将自动删除子表中被引用键值与主表中相对应的外键值相同的记录。如果 ON DELETE SET NULL 被指定，子表中的外键对应行将被设置为 NULL 值。
与 SQL 标准不一致： if ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the SAME TABLE it has already updated during the cascade, it acts like RESTRICT. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, works starting from 4.0.13. A self-referential ON DELETE CASCADE has always worked.
示例：
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;
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
