MYSQL数据库(八)- 修改数据表添加约束

时间:2022-11-23 14:43:38

本章目录

  • 添加约束目录

一、添加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
MYSQL数据库(八)- 修改数据表添加约束

| 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