SQL约束操作相关

最新推荐文章于 2024-09-12 01:15:37 发布

q6301031

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量152

点赞数 1

分类专栏： SQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/q6301031/article/details/107592379

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL约束

约束

约束

对表中的数据进行限定,保证数据的完整性、有效性和正确性

非空约束和唯一键约束

非空约束 not null 此列的值无法为空 
唯一键约束 unique 此列的值无法重复
添加方式
1.创建表时添加
create table if not exists student(
	name varchar(10) not null unique
	** 唯一键还可以在所有字段之后添加 unique key (字段名)
)
2.创建表之后添加
alter table student modify name varchar(10) not null unique;
 ** 可选择性添加一个或多个约束 
删除方式
非空约束的删除
alter table student modify name varchar(10);
唯一键删除
alter table student drop index name;


结果演示 (以第一种为例,可自行插入数据测试)
mysql> create table if not exists student(
    -> name varchar(10) not null unique); -- 添加非空、唯一键约束
Query OK, 0 rows affected (0.13 sec)

mysql> desc student;  -- 查看表结构 这里因为只有一列数据
	-- 非空 唯一 俩个约束当成主键显示了 再添加主键即可变为原来的定义
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> alter table student drop index name; -- 删除唯一键约束
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student modify name varchar(10); -- 删除非空约束
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student; -- 查看操作后结果变化
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)

主键约束和自动增长

主键约束 primary key 只能有一个,非空且唯一
自动增长 auto_increment 通常与主键一起使用,不插入数据时候在上一条的基础上加1
添加方式
1.创建表时候添加
create table if exists student (
	id int primary key auto_increment
	** 也可以在所有字段列表之后添加 primary key (字段名)
	** 客户端默认这种方式,包括唯一键的创建 可查看建表语句验证
)
2.创建之后添加
alter table student modify id int primary key auto_increment;
删除方式
alter table sutdent modify in int; 
 ** 只可删除自增长
alter table sutdent drop primary key;
 ** 删除主键


结果演示(以第二种方式,使用上面的一张表)
mysql> alter table student add id int primary key auto_increment;
         -- 添加一列,同时定义为主键且自增长
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student; -- 查看结果 这里唯一键就显示出来了
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| name  | varchar(10) | NO   | UNI | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> alter table student modify id int; -- 删除自增长
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student drop primary key; -- 删除主键
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;  -- 查看修改结果
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   | PRI | NULL    |       |
| id    | int(11)     | NO   |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

外键约束

如果一张表中某些列存在大量重复的数据,直接存入会更加占容量,也不太好维护
这时就需要把相同的列分离到另一张表中,分离之后还需要指定俩张表之间的关系,
否则则会出现预期外错误,例如学生表中显示班级在班级不存在,某人注册过账户,账户却消失了等

外键:使表与表之间存在关系,保证数据安全性和准确性
创建语法
1.创建表时添加
在所有列之后
constraint 外键名 foreign key (从表外键列) references 主表名(主表列名[一般为主键]) on 外键约束;
 ** 如果不指定外键名 则会默认生成
 ** 外键约束  默认为严格模式 不允许更改与从表关联的列的信息
 	** 级联模式 on delete cascade 删除级联 修改主表在从表内关联的列的信息,从表对应列跟着改变
 			    on update cascade 修改级联
 	** 置空模式 on delete set null  如果删除主表某些列 与主表关联的从表对应信息置为空
2.创建表后添加
alter table 表名 constraint 外键名 foreign key (从表外键列) references 主表名(主表列名[一般为主键]) on 外键约束;
删除外键
alter table 表名 drop foreign key 外键名;
 ** 这里通过这种方式删除外键之后,外键效果已经失效,但是通过desc查看表结构时会出现外键对应
 ** 列依然存在的情况,原因是创建外键时会添加一个外键索引,虽然外键删除了,但索引依然存在,主键
 ** 也类似 可以通过 show index from 表名; 查看对应表的索引 
 ** drop index 索引名 on 表名; 即可删除


操作结果演示(以表后添加为例)
创建了俩张表
student表 
+------+----+----------+
| name | id | class_id |
+------+----+----------+
| 张三     |  1 |        1 |
| 李四     |  2 |        2 |
| 王五     |  3 |        1 |
| 赵六     |  4 |        2 |
+------+----+----------+
class表 (id为主键)
+----+------+
| id | name |
+----+------+
|  1 | 一班    |
|  2 | 二班     |
+----+------+

mysql> alter table student add constraint stu_class foreign key (class_id) references class(id) on delete set null;
		-- 将从表student表的class_id列设为外键并且与主表class的主键id列关联、设置删除置空模式
		-- 可以尝试下在从表插入信息 class_id列在主表中不存在 会发现无法插入
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> delete from class where id=1;  -- 删除主表的id=1的行
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;  -- 查看从表的变化
+------+----+----------+
| name | id | class_id |
+------+----+----------+
| 张三     |  1 |     NULL |
| 李四     |  2 |        2 |
| 王五     |  3 |     NULL |
| 赵六     |  4 |        2 |
+------+----+----------+
4 rows in set (0.00 sec)

mysql> alter table student drop foreign key stu_class; -- 删除外键关联
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;    -- 查看表结构可以看到class_id的外键依然显示
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(10) | NO   | PRI | NULL    |       |
| id       | int(11)     | NO   |     | 0       |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> drop index stu_class on student;  -- 删除表的外键索引
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;  -- 查看表结构,class_id的外键消失
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(10) | NO   | PRI | NULL    |       |
| id       | int(11)     | NO   |     | 0       |       |
| class_id | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)