本章目录
- 添加约束目录
一、添加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表格
//插入前
+----------+----------------------+------+-----+---------+--