实例一：
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)