MYSQL数据库（八）- 修改数据表添加约束

本章目录

添加约束目录

一、添加id列,无主键无约束，准备工作

二、给city2表添加主键约束（任何一张表只能有一个主键）

三、添加唯一约束

四、添加外键约束

五、添加和/删除默认约束

删除约束目录

一、删除主键约束

二、删除唯一约束

三、删除外键约束

---

本章重要语句

---

本章案例：

准备工作，创建一个city2数据表，不添加任何主键和约束

创建一个city2数据表，如下 mysql> create table city2( -> username varchar(20) NOT NULL, -> pid smallint unsigned); Query OK, 0 rows affected (0.12 sec)//创建成功 mysql> show columns from city2;//查看数据表city2，而且没有添加主键和约束。 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+

——————————–添加约束————————

一、添加id列,还没有添加主键

语句：alter table city2 add id smallint unsigned first; 解析：更改数据表city2,添加id列，类型smallint ，无符号类型，位于列顶

ysql> alter table city2 add id smallint unsigned first; Query OK, 0 rows affected (0.30 sec)//添加成功 mysql> show columns from city2;//验证插入结果 //插入前 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ //插入后 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | YES | | NULL | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+

---

二、给city2表添加主键约束（任何一张表只能有一个主键）

什么是主键学习：http://blog.csdn.net/bobo89455100/article/details/72626337

语句：alter table city2 add constraint ky_city2_id PRIMARY KEY(id) 解析：更改列表city2，添加约束，约束名称：ky_city2_id 主键列（id列表）

mysql> alter table city2 add constraint ky_city2_id PRIMARY KEY(id); Query OK, 0 rows affected (0.32 sec)//添加主键成功 mysql> show columns from city2;//查看city2表格 //插入前 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | YES | | NULL | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ //插入后，id主键添加成功,看key列。 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+

---

三、添加唯一约束

什么是唯一约束学习：http://blog.csdn.net/bobo89455100/article/details/72626337

注意：和主键约束不一样的是，主键约束只能有一个，而唯一约束可以为多个

语句：alter table city2 add unique(username)

解析：更改表格city2，添加唯一约束（username列） mysql> alter table city2 add unique(username); Query OK, 0 rows affected (0.25 sec)//添加唯一约束成功 mysql> show columns from city2;//查看city2表格 //添加前 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ //添加后，username列后面的唯一约束添加成功 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

---

四、添加外键约束

语句：alter table city2 add foreign key(pid) references sheng(id) 解析：更改表格city2,添加外键约束（pid列）参照数据表“sheng”(id)列

mysql> alter table city2 add foreign key(pid) references sheng(id); Query OK, 0 rows affected (0.32 sec)//插入添加成功 mysql> show columns from city2;//查看city2数据表 //插入前pid是没有外键约素 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ //插入前pid是外键约素插入成功 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | +----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

---

五、添加和/删除默认约束

语句：alter table tab_name alter[column] col_name{set default literal|drop default} 解析：更改 数据 某某表，更改【列】 某某列名称{添加默认约束或者删除默认约束}

1、向数据表中添加一个新列age,不指定默认值 mysql> alter table city2 add age tinyint UNSIGNED NOT NULL; Query OK, 0 rows affected (0.30 sec) //插入成功 mysql> SHOW COLUMNS FROM CITY2; //插入前 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | +----------+----------------------+------+-----+---------+-------+ //插入后，age列存在，默认为null +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) 2、添加默认值（age赋值为15） mysql> alter table city2 alter age set default 15; Query OK, 0 rows affected (0.15 sec)//添加成功 mysql> show columns from city2; //插入前age默认值为null +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ //插入后默认值为15 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 15 | | +----------+----------------------+------+-----+---------+-------+ 三、删除默认值 mysql> alter table city2 alter age drop default ; Query OK, 0 rows affected (0.18 sec) //成功 mysql> show columns from city2; //删除前,默认值为15 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 15 | | +----------+----------------------+------+-----+---------+-------+ //删除后,age的默认值为null +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

---

——————————–删除约束————————

一、删除主键约束

语句：alter table tal_name drop primary key

解析：更改 某某数据表 删除 主键

注意：删除住家的时候，drop后面为什么不用添加列的名称，因为任何一张表只有一个主键，所以我们无需去添加。

mysql> alter table city2 drop primary key; Query OK, 0 rows affected (0.25 sec)//删除主键成功 mysql> show columns from city2;//查看数据表city2 //删除主键前 id存在主键key存在 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ //删除主键后，id的主键key已经被删除 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | PRI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

---

二、删除唯一约束

语句：alter table tal_name drop{index|key} index_name解释：更改 某某数据表 删除{index|ke} 列名称 //删除username的唯一约束 mysql> alter table city2 drop index username; Query OK, 0 rows affected (0.29 sec)//删除username的唯一约束成功 mysql> show columns from city2;//查看表结构 //删除前，username的唯一约束存在 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | PRI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ //删除前，username的唯一约束key已经不存在，但是username这个参数还存在。 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+

---

三、删除外键约束

语句：alter table tbl_name drop forign key fk_symbol（fk_symbol：外键名称，自己定义的）;解析更改 某某数据表 删除 外键约束 外简约束名称

如何查看我们之前创建外键约束的名称？

语句：show create table city2; //查看创建的表格city2

| city2 | CREATE TABLE `city2` ( `id` smallint(5) unsigned NOT NULL DEFAULT '0', `username` varchar(20) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `age` tinyint(3) unsigned NOT NULL, KEY `pid` (`pid`), CONSTRAINT `city2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `sheng` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

知道外键约束的名称之后，我们就可以删除这个约束，如下： alter table city2 drop forign key city2_ibfk_1;//更改数据表city2,删除外键约束city2_ibfk_1