MySQL学习笔记3：约束以及修改数据表

本文知识点概括如下：

外键约束的要求解析：

约束的作用和种类，如下：

列级约束：约束只针对于某一个字段。。
表级约束：约束针对于2个或2个以上的字段。
FOREIGN KEY
外键约束的作用：保持数据的一致性和完整性；实现数据表的一对一或一对多的关系。
外键列：加过FOREIGN KEY关键词的列。
参照列：外键列所参照的那一列。
子表：具有外键列的表。
父表：子表所参照的表。
外键约束的要求，如下：

外键列和参照列必须具有相似的数据类型。若为数字，则数字的 长度和是否有符号位 必须完全一致；
若为字符，则字符的长度可以不同。
外键列和参照列必须创建索引。如果外键列上没有索引的话，MySQL将自动创建索引。
而参照列没有索引的话，MySQL则不会自动创建索引！
编辑数据表的默认存储引擎
打开MySQL的配置文件 my.ini ，修改如下所示的行（将默认的存储引擎修改为INNODB）：
[mysqld]
default-storage-engine=INNODB
修改完配置文件，记得重新启动MySQL服务！

mysql> CREATE TABLE provinces(   #创建父表（子表所参照的表）
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, # id被定义为主键，而主键列会自动创建索引！
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW CREATE TABLE provinces; #可以看到，数据表provinces的存储引擎确实为InnoDB
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                              |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE users(  #创建子表（具有外键列的表）
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED, #外键列pid与参照列id的类型都是SMALLINT UNSIGNED，符合要求。
    -> FOREIGN KEY (pid) REFERENCES provinces(id) #pid是外键列,数据表provinces中的id是参照列。
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW INDEXES FROM provinces\G; #显示索引（参照列id被定义为主键，会自动创建索引）
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id   # id被定义为主键，主键列会自动创建索引！
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM users\G; #可以看到，数据表users中，存在2个索引。
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id   #第一个索引（数据表users中的id，被定义为主键，会自动创建索引！）
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid   
 Seq_in_index: 1
  Column_name: pid  #第二个索引（如果外键列上没有索引的话，MySQL将自动创建索引。）
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),  #id被定义为主键，而主键列会自动创建索引。
  KEY `pid` (`pid`),   #pid被定义为外键列，系统会为外键列自动添加索引。
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
外键约束的参照操作，如下：

CASCADE使用示例：
mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE #父表中的删除或更新操作，同步到子表。
    -> );
Query OK, 0 rows affected (0.26 sec)

mysql> SHOW CREATE TABLE users1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users1 | CREATE TABLE `users1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE    
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |                                #父表中的删除或更新操作，同步到子表。
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

先在父表中插入记录，然后才能在子表中插入记录！（注意插入记录的顺序）
mysql> INSERT provinces(pname) VALUES('A'); #向父表中插入记录
Query OK, 1 row affected (0.09 sec)

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

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

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

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

mysql> INSERT users1(username,pid) VALUES('John',7);#记录插入失败，因为父表中id=7的记录不存在（插入失败，但占用了一个编号）。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)
mysql> INSERT users1(username,pid) VALUES('John',1);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT users1(username,pid) VALUES('Rose',3);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users1; #第2次插入记录时，插入失败。占用了一个编号2。
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    3 |
|  3 | John     |    1 |
|  4 | Rose     |    3 |
+----+----------+------+
3 row