MySQL的外键约束

为什么80%的码农都做不了架构师？>>>   

MySQL的外键约束，与其它数据库的实现有非常大的区别。它仅仅作为“注释”处理，并不会真正意义上对写入的数据进行检查。

A foreign key constraint is not required merely to join two tables. For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table. It is extremely important to realize when using this syntax that:

• MySQL does not perform any sort of CHECK to make sure that col_name actually exists in tbl_name (or even thattbl_name itself exists).

• MySQL does not perform any sort of action on tbl_name such as deleting rows in response to actions taken on rows in the table which you are defining; in other words, this syntax induces no ON DELETE or ON UPDATE behavior whatsoever. (Although you can write an ON DELETE or ON UPDATE clause as part of the REFERENCES clause, it is also ignored.)

• This syntax creates a column; it does not create any sort of index or key.

mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.12-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE person (
    ->     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     name CHAR(60) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> CREATE TABLE shirt (
    ->     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    ->     color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    ->     owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> INSERT INTO person VALUES (NULL, 'Antonio Paz');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT @last := LAST_INSERT_ID();
+---------------------------+
| @last := LAST_INSERT_ID() |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO shirt VALUES
    -> (NULL, 'polo', 'blue', @last),
    -> (NULL, 'dress', 'white', @last),
    -> (NULL, 't-shirt', 'blue', @last);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT @last := LAST_INSERT_ID();
+---------------------------+
| @last := LAST_INSERT_ID() |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO shirt VALUES
    -> (NULL, 'dress', 'orange', @last),
    -> (NULL, 'polo', 'red', @last),
    -> (NULL, 'dress', 'blue', @last),
    -> (NULL, 't-shirt', 'white', @last);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> select * from shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> select * from person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> insert into shirt values(NULL,'skirt',
    -> 'red',3);
ERROR 1265 (01000): Data truncated for column 'style' at row 1
mysql> select * from shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+
7 rows in set (0.01 sec)

mysql> show create table shirt \g
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| shirt | CREATE TABLE `shirt` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `style` enum('t-shirt','polo','dress') NOT NULL,
  `color` enum('red','blue','orange','white','black') NOT NULL,
  `owner` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into shirt values (NULL,'polo','red',3);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> select * from shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
|  8 | polo    | red    |     3 |
+----+---------+--------+-------+
8 rows in set (0.00 sec)

转载于:https://my.oschina.net/huayd/blog/141100