MySQL-约束和数据表操作

外键约束的要求解析
外键约束的参照操作
表级约束与列级约束
修改数据表-添加/删除列
修改数据表-添加约束
- 添加/删除默认约束
修改数据表-删除约束
- 删除主键约束
- 删除唯一约束
修改数据表-修改列定义和更名数据库
总结

外键约束的要求解析

约束保证数据的完整性和一致性
约束分为表级约束和列级约束
约束类型包括：
1. NOT NULL(非空约束)
2. PRIMARY KEY(主键约束)
3. UNIQUE KEY(唯一约束)
4. DEFAULT(默认约束)
5. FOREIGN KEY(外键约束)

FOREIGN KEY：

保持数据一致性，完整性。
实现一对一或一对多关系。

外键约束的要求

父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能为InnoDB。
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

编辑数据表的默认存储引擎

修改MySQL配置文件：

default-storage-engine=INNODB

演示外键约束

创建数据表：

mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

查看数据表DB类型：

mysql> SHOW CREATE TABLE provinces;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------	------------------------+
| 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.02 sec)

创建一个users表，建立外键约束：

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint

出现错误，因为users表的pid和provinces表中的id字段的数据类型不一致。

创建users表，字段和provinces表id字段数据类型一致，但默认是有符号位的：

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint

依然报错，所以符号位也必须要一致。

再次修改新建表的sql:

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );
Query OK, 0 rows affected (0.11 sec)

创建users表成功。

这时，users表为子表，provinces为父表；user.pid为外键列，provinces.id为参照列。

查看参照列索引：

mysql> SHOW INDEXES FROM provinces\G;
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

注：\G为查询结果按列打印。

查看外键列索引：

mysql> SHOW INDEXES FROM userS \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: 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
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.01 sec)

ERROR:
No query specified

查看users表的表结构：

mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: 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`),
  KEY `pid` (`pid`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

外键约束的参照操作

CASCADE:从父表或更新且自动删除或更新子表中匹配的行
SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。
RESTRICT:拒绝对父表的删除或更新操作。
NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同。

创建一个新表：

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.11 sec)

查看表结构：

mysql> SHOW CREATE TABLE users1\G;
*************************** 1. row ***************************
       Table: users1
Create Table: 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.00 sec)

ERROR:
No query specified

在provinces表中插入几条数据：

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

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

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

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

在users1表插入记录：

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

mysql> INSERT users1(username,pid) VALUES('John',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
ails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCE
 `provinces` (`id`) ON DELETE CASCADE)

第二条记录出错，因为pid为7，在父表中不存在，所以出错。

继续插入两条数据：

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

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

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

中间插入失败一次，id值也会递增1。

删除id为3的记录：

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

查看父表和子表的结果：

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

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  3 | Rose     |    2 |
+----+----------+------+
1 row in set (0.00 sec)

表级约束与列级约束

对一个数据列建立的约束，称为列级约束。
对多个数据列简历的约束，称为标记约束。
列级约束既可以在列定义时声明，也可以在列定义后声明。
表级约束只能在列定义后声明。

修改数据表-添加/删除列

添加单列

语法：

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

查看users1表结构：

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

增加一列：

mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

再增加一列：

mysql> ALTER TABLE users1 ADD password1 VARCHAR(32) NOT NULL AFTER username;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username  | varchar(10)          | NO   |     | NULL    |                |
| password1 | varchar(32)          | NO   |     | NULL    |                |
| pid       | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age       | tinyint(3) unsigned  | NO   |     | 10      |                |
+-----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

再插入一列，并查看表结构：

mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| truename  | varchar(20)          | NO   |     | NULL    |                |
| id        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username  | varchar(10)          | NO   |     | NULL    |                |
| password1 | varchar(32)          | NO   |     | NULL    |                |
| pid       | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age       | tinyint(3) unsigned  | NO   |     | 10      |                |
+-----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

添加多列

语法：

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition...)

删除列

ALTER TABLE tbl_name DROP col_name;

删除多列： ALTER TABLE tbl_name DROP col_name1，DROP col_name2;

修改数据表-添加约束

添加主键约束语法：

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

创建一个新表：

mysql> CREATE TABLE users2(
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.09 sec)

添加一个字段：

mysql> ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加主键：

mysql> ALTER TABLE users2 ADD CONSTRAINT PE_users2_id PRIMARY KEY (id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加唯一约束：

mysql> ALTER TABLE users2 ADD UNIQUE (username);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

修改数据表-删除约束

删除主键约束

语法：

ALTER TABLE tbl_name DROP PRIMARY KEY

删除唯一约束

语法：

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

修改数据表-修改列定义和更名数据库

修改列定义：

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

数据表更名：

方法1：ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
方法2: RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

总结

约束
- 功能
  - NOT NULL(非空约束)
  - PRIMARY KEY(主键约束)
  - UNIQUE KEY(唯一约束)
  - DEFAULT(默认约束)
  - FOREIGN KEY(外键约束)
- 数据列的数目
  - 表级约束
  - 列级约束
修改数据表
- 针对字段的操作：添加/删除字段、修改列定义，修改列名称等；
- 针对约束的操作：添加/删除各种约束
- 针对数据表的操作：数据表更名（两种方式）