MYSQL字段约束之外建约束参考操作

MYSQL字段约束之外建约束参考操作
1，CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
    格式： ON UPDATE | DELETE CASCADE 
2, SET NULL: 从父表删除或更新，并设置子表中的外键列为null。如果使用该选项必须保证子表列没有指定NOT NULL；
3，RSETRICT: 拒绝对附表的删除或更新操作
4，NO ACTION : 标准SQL的关键字，在MYSQL中与RESTRICT相同；

1，创建从表
mysql> CREATE TABLE user1(
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username CHAR(10) NOT NULL,
    -> cid INT UNSIGNED,
    -> FOREIGN KEY (cid) REFERENCES corse (id) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.10 sec)


2，从表自动创建显示，设置物理外键约束
CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `corse` (`id`) ON DELETE CASCADE
mysql> show create table user1;
| user1 | CREATE TABLE `user1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` char(10) NOT NULL,
  `cid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `corse` (`id`) ON DEL
ETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

3，主表插入属性值；
mysql> SELECT * FROM  corse;
+----+------------+
| id | name       |
+----+------------+
|  1 | shuaishuai |
|  2 | shuaige    |
|  3 | shuaibi    |
|  4 | dashuai    |
+----+------------+
4 rows in set (0.00 sec)

4，从表插入属性值；
mysql> select * from user1;
+----+-----------+------+
| id | username  | cid  |
+----+-----------+------+
|  1 | daxiong   |    1 |
|  2 | jingxiang |    2 |
|  3 | jiqimao   |    3 |
|  4 | 多啦爱梦  |    4 |
+----+-----------+------+
4 rows in set (0.05 sec),

5，删除主表中的行值；
mysql> delete from corse WHERE id=1;

6，查询从表中的变化；对应的外键值也被删除或更新；
mysql> select * from corse;
+----+---------+
| id | name    |
+----+---------+
|  2 | shuaige |
|  3 | shuaibi |
|  4 | dashuai |
+----+---------+
3 rows in set (0.00 sec)

mysql> select * from user1;
+----+-----------+------+
| id | username  | cid  |
+----+-----------+------+
|  2 | jingxiang |    2 |
|  3 | jiqimao   |    3 |
|  4 | 多啦爱梦  |    4 |
+----+-----------+------+
3 rows in set (0.00 sec)