3-6：MySQL 修改数据表–添加约束

一.  添加主键约束

添加主键约束的语法结构：

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

创建一个测试用的数据表users2：

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

首先增加一列：

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

给id列添加主键约束：

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

其中PK_users2_id为自定义约束的名字，可以不写。

接下来看一下数据表的结构：

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 | 0       |       |
+----------+----------------------+------+-----+---------+-------+

可以发现，id已经为主键

二.  添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type]
(index_col_nam,...)

唯一约束可以有多个，我们给usersname添加唯一约束：

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

然后查看一下user2表的创建命令：

mysql> SHOW CREATE TABLE users2;
+--------+----------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                            |
+--------+----------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `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)

可以看到username已经是唯一约束

三.  添加外键约束

添加外键约束的语法结构：

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition

想要users2中的pid参照provinces中id，则需要给users2中的pid设置外键约束：

mysql> ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

接下来看一下数据表的结构：

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| 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       |       |
+----------+----------------------+------+-----+---------+-------+

可以看到pid已经是外键约束

四.  添加/删除默认约束

添加/删除默认约束的语法结构：

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

添加一个age字段：

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

查看数据表的结构：

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| 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)

可以看到age字段并没有默认值，接下来添加默认约束：

mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看数据表的结构：

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| 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.00 sec)

可以看到age字段默认值已经为15，接下来删除默认约束：

mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| 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.00 sec)

可以看到默认值已经被删除