外键约束的参照操作

实例一：

mysql> CREATE TABLE users2(
-> id SMALLINT PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY(pid) REFERENCES province (id) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.13 sec)

向父表写入记录

mysql> INSERT province(pname) VALUES('A');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT province(pname) VALUES('B');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT province(pname) VALUES('C');
Query OK, 1 row affected (0.22 sec)

mysql> SELECT * FROM province;
+----+-------+
| id   |pname|
+----+-------+
| 1    | A      |
| 2    | B      |
| 3    | C      |
+----+-------+
3 rows in set (0.02 sec)

向子表写入记录

mysql> INSERT users2(username, pid) VALUES('Tom',3);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT users2(username, pid) VALUES('John',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`users2`, CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES
`province` (`id`) ON DELETE CASCADE)
mysql>
mysql> INSERT users2(username, pid) VALUES('John',3);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM users2;
+----+----------+------+
| id  |username| pid  |
+----+----------+------+
| 1   | Tom      | 3      |
| 3   | John      | 3      |
+----+----------+------+
2 rows in set (0.00 sec)

删除父表中的值，子表也会发生变化

mysql> DELETE FROM province WHERE id=3;
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM province;
+----+------- +
| id  | pname |
+--- +-- ----- +
| 1   | A        |
| 2   | B        |
+----+------- +
2 rows in set (0.00 sec)

mysql> SELECT * FROM users2;
Empty set (0.00 sec)