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

本文知识点概括如下：

外键约束的要求解析：

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

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

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

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

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

外键列和参照列必须具有相似的数据类型。若为数字，则数字的长度和是否有符号位必须完全一致；
若为字符，则字符的长度可以不同。
外键列和参照列必须创建索引。如果外键列上没有索引的话，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)
外键约束的参照操作，如下：

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

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 rows in set (0.00 sec)

mysql> DELETE FROM provinces WHERE id=3; #删除父表中id=3的记录（删除操作会同步到子表中的相应记录）
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM provinces; #可以看到，父表中id=3的记录已经被删除。
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users1; #子表中外键列pid=3的记录，已经被删除。
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 3 | John | 1 |
+----+----------+------+
1 row in set (0.00 sec)

SET NULL 使用示例：
mysql> CREATE TABLE users2(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE SET NULL #删除父表记录时，将子表的相应记录的外键列pid设置为NULL。
-> );
Query OK, 0 rows affected (0.10 sec)

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

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

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

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

mysql> INSERT users2(username,pid) VALUES('Jerry',4);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users2(username,pid) VALUES('Marry',4);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users2;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | Tom | 1 |
| 2 | Jerry | 4 |
| 3 | Marry | 4 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> DELETE FROM provinces WHERE id=4; #删除父表中id=4的记录，同时将子表中外键列pid=4的记录设置为NULL
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM provinces;#可以看到，父表中id=4的记录已经被删除。
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users2;#子表中pid=4的记录已经被设置为NULL
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | Tom | 1 |
| 2 | Jerry | NULL |
| 3 | Marry | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

RESTRICT使用示例：
mysql> CREATE TABLE users3(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE RESTRICT #拒绝对父表的删除或更新操作。
-> );
Query OK, 0 rows affected (0.11 sec)

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

mysql> INSERT users3 (username,pid) VALUES('Tome',1);#向子表中插入记录
Query OK, 1 row affected (0.06 sec)

mysql> INSERT users3 (username,pid) VALUES('Jerry',2);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM users3;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | Tome | 1 |
| 2 | Jerry | 2 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> DELETE FROM provinces WHERE id=2;#删除失败（RESTRICT拒绝对父表的删除或更新操作）
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`users3`, CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`))

NO ACTION 使用示例（在MySQL中NO ACTION与RESTRICT作用相同）：
mysql> CREATE TABLE users4(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE NO ACTION #拒绝对父表的删除或更新操作
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
+----+-------+
2 rows in set (0.00 sec)

mysql> INSERT users4(username,pid) VALUES('Tom',1); #向子表中插入记录
Query OK, 1 row affected (0.03 sec)

mysql> INSERT users4(username,pid) VALUES('Jerry',2);
Query OK, 1 row affected (0.05 sec)

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

mysql> DELETE FROM provinces WHERE id=2;#删除失败（NO ACTION拒绝对父表的删除或更新操作）
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`users3`, CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`))
在实际开发中，我们很少使用物理的外键约束，大多使用逻辑的外键约束。
因为只有INNODB这一种存储引擎支持物理的外键约束，所以，在实际开发中，我们一般不去定义物理的外键约束。
所谓的逻辑外键，指定就是，我们在定义2张表的结构的时候，按照存在的某种结构的方式去定义，但是不使用FOREIGN KEY关键字。

表级约束与列级约束，如下：

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

在实际开发中，列级约束使用较多，表级约束很少使用。本文中前面出现的约束都是列级约束（在列定义之后声明的列级约束）！！
其中，NOT NULL约束和DEFAULT约束，只有列级约束（没有表级约束）！
而主键约束，唯一约束和外键约束，既有列级约束，又有表级约束！

修改数据表
添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
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.06 sec)

mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;#默认情况下，将列添加到最后。
Query OK, 1 row affected (0.27 sec)
Records: 1 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 | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username; #将password列添加到username列的后面。
Query OK, 1 row affected (0.23 sec)
Records: 1 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 | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST; #将truename列添加到最前面。
Query OK, 1 row affected (0.22 sec)
Records: 1 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 | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

添加多列（添加多列时，只能添加到数据表的后面！）
ALTER TABLE tbl_name ADD [COLUMN] (col_name1 column_definition1,……)
mysql> ALTER TABLE users1 ADD COLUMN (sex ENUM('woman','man') NOT NULL,score FLOAT(4,2) NOT NULL);#添加多列
Query OK, 1 row affected (0.23 sec)
Records: 1 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 | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | enum('woman','man') | NO | | NULL | |
| score | float(4,2) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

mysql> ALTER TABLE users1 DROP truename; #删除truename列
Query OK, 1 row affected (0.23 sec)
Records: 1 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 | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | enum('woman','man') | NO | | NULL | |
| score | float(4,2) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

mysql> ALTER TABLE users1 DROP password,DROP age; #删除password列和age列。
Query OK, 1 row affected (0.22 sec)
Records: 1 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 | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| sex | enum('woman','man') | NO | | NULL | |
| score | float(4,2) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE users1 ADD (number INT NOT NULL,height FLOAT(5,2)),DROP sex,DROP score; #添加列和删除列同时进行。
Query OK, 1 row affected (0.26 sec)
Records: 1 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 | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| number | int(11) | NO | | NULL | |
| height | float(5,2) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

修改数据表
添加主键约束（主键约束只能有1个）
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
mysql> CREATE TABLE users5( #创建一个表
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED
-> );
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE users5;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users5 ADD id SMALLINT UNSIGNED;#添加id列
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

mysql> ALTER TABLE users5 ADD CONSTRAINT PK_users5_id PRIMARY KEY(id);#为id列添加主键约束。
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5; #此时，id列已经被设置为主键。
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

添加唯一约束（唯一约束可以有多个）
ALTER TABLE tbl_name ADD [CONSTRAINT[sysbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……)
mysql> ALTER TABLE users5 ADD UNIQUE (username); #为username列添加唯一约束
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users5;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`), #主键约束
UNIQUE KEY `username` (`username`) #唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加外键约束（外键约束可以有多个）
ALTER TABLE tbl_name ADD [CONSTRAINT[sysbol]] FOREIGN KEY [index_name](index_col_name,……) reference_definition
mysql> SHOW COLUMNS FROM provinces;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> ALTER TABLE users5 ADD FOREIGN KEY (pid) REFERENCES provinces(id);#添加外键约束
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users5;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`), #主键约束
UNIQUE KEY `username` (`username`), #唯一约束
KEY `pid` (`pid`),
CONSTRAINT `users5_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) #外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}
mysql> ALTER TABLE users5 ADD age TINYINT UNSIGNED NOT NULL; #添加字段age
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5; #可以看到，age字段的Default为NULL，表示没有默认值
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
mysql> ALTER TABLE users5 ALTER age SET DEFAULT 15; #为age列添加默认约束（设置默认值为15）
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5; #可以看到age字段被设置了默认值15
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(3) unsigned | NO | | 15 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE users5 ALTER age DROP DEFAULT; #删除默认约束
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5;#可以看到age字段的Default重新变为NULL，表示没有默认值（删除了默认约束）
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE users5 DROP PRIMARY KEY;#删除主键约束；主键只能有1个，因此不需要加主键的名字。
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users5;#可以看到,id已经不再是主键约束了。
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | PRI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| id | smallint(5) unsigned | NO | | 0 | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name #唯一约束可以有多个，所以要指出具体的index_name
mysql> SHOW INDEXES FROM users5\G; #查看约束的名字
*************************** 1. row ***************************
Table: users5
Non_unique: 0
Key_name: username #此处的username是索引名（或称为约束名）
Seq_in_index: 1
Column_name: username #username列被定义为主键，系统会为主键自动创建索引。
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users5
Non_unique: 1
Key_name: pid #此处的pid是索引的名字（或称为约束名）
Seq_in_index: 1
Column_name: pid #pid列是外键列，系统会自动为外键列创建索引。
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.04 sec)

mysql> ALTER TABLE users5 DROP INDEX username; #删除username约束（username字段还在）
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5; #可以看到，username字段仍然存在，但主键约束已经不在了。
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| id | smallint(5) unsigned | NO | | 0 | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> SHOW INDEXES FROM users5\G; #现在，只剩下1个约束了（主键约束已经被删除）
*************************** 1. row ***************************
Table: users5
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:
1 row in set (0.00 sec)

删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol #外键约束可以有多个，因此，必须要加上约束的名称。
mysql> SHOW CREATE TABLE users5; #查看外键约束的名称
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL DEFAULT '0',
`age` tinyint(3) unsigned NOT NULL,
KEY `pid` (`pid`),
CONSTRAINT `users5_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)#此处的users5_ibfk_1 就是外键约束的名称。
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users5 DROP FOREIGN KEY users5_ibfk_1; #删除外键约束
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE users5; #可以看到，外键约束已经被删除。但pid列上的索引还在！
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL DEFAULT '0',
`age` tinyint(3) unsigned NOT NULL,
KEY `pid` (`pid`) #pid字段上的索引还存在
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users5 DROP INDEX pid; #此处的pid是索引名。（删除pid列上的索引）
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users5; #可以看到pid列上的索引已经被删除。
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL DEFAULT '0',
`age` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改列定义（列名保持不变，只修改列的定义）
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
mysql> SHOW COLUMNS FROM users5; #当前id列位于第3行
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | | 0 | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE users5 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; #将id列移动到最前面
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE users5 MODIFY id TINYINT UNSIGNED NOT NULL;#修改id列的定义（数据类型修改为TINYINT）
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.05 sec)
修改数据类型时，要注意：将大类型修改为小类型，有可能会造成数据的丢失！
修改列名称（使用CHANGE既可以修改列名称，又可以修改列定义）
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
mysql> ALTER TABLE users5 CHANGE pid p_id TINYINT UNSIGNED NOT NULL; #修改pid列的列名称和列定义
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users5;#列名称修改为p_id，列的数据类型修改为TINYINT
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| p_id | tinyint(3) unsigned | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
数据表更名
方法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,……]

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| provinces |
| users |
| users1 |
| users2 |
| users3 |
| users4 |
| users5 |
+----------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE users5 RENAME users55; #将数据表users5更名为users55
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| provinces |
| users |
| users1 |
| users2 |
| users3 |
| users4 |
| users55 |
+----------------+
7 rows in set (0.00 sec)
mysql> RENAME TABLE users1 TO users11,users3 TO users33;#同时更名多个数据表；
Query OK, 0 rows affected (0.16 sec)

mysql> SHOW TABLES;#可以看到，已经将表users1更名为users11,将表users3更名为users33;
+----------------+
| Tables_in_test |
+----------------+
| provinces |
| users |
| users11 |
| users2 |
| users33 |
| users4 |
| users55 |
+----------------+
7 rows in set (0.00 sec)
注意：我们应尽量少使用数据列（字段）的更名以及数据表的更名！
本文的知识点总结如下：

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

（完）

秒客网

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

相关文章