约束(constraint)
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性
约束分类
非空约束：not null
- 非空约束，针对某个字段设置其值不为空
唯一性约束: unique
- 唯一性约束，它可以使某个字段的值不能重复 , 如果没有指定 not null , 则 unique 字段可以有多个 null , 因为null不是具体的值 , 所以不算重复
- 一张表可以有多个 unique 字段
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（不建议使用 , mysql不支持，oracle支持）
表级约束和列级约束
约束直接添加到列后面的叫做列级约束 , 直接在字段名后指定: 字段名 约束名
- 列级约束主要是给一个字段添加某一个约束
create table t_vip(
id int,
--not null只有列级约束，没有表级约束
name varchar(255) not null
);
约束直接在在表定义的最后添加的约束被称为表级约束: 约束名(列名1…)
- 表级约束主要是给多个字段联合起来添加某一个约束(也可以是一个字段)
- not null只有列级约束，没有表级约束
create table t_vip(
id int,
name varchar(255),
email varchar(255),
--约束没有添加在列的后面，这种约束被称为表级约束
unique(name,email)
);
not null约束
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zdwOksyy-1676261205990)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666962353865.png)]
测试非空约束
drop table if exists t_vip;
create table t_vip(
id int,
--not null只有列级约束，没有表级约束
name varchar(255) not null
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
--ERROR 1364 (HY000): Field 'name' doesn't have a default value
insert into t_vip(id) values(3);
unique约束
一张表可以有多个 unique 字段 , 另外被unique约束的字段如果没有指定 not null约束 , 则可以插入多个NULL不算重复(null不是具体的值)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oyDlCiDC-1676261205991)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666962372902.png)]
测试唯一约束
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
-- ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'
insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
--name字段虽然被unique约束了，但是可以为NULL
--如果没有指定 not null , 则unique字段可以有多个null不算重复
insert into t_vip(id) values(4);
insert into t_vip(id) values(5);
unique 和 not null 联合使用
在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段 , 但是Oracle中不一样
create table t_vip(
id int,
name varchar(255) not null unique
);
mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
两个字段具有各自唯一性
--这样创建表示：name具有唯一性，email具有唯一性
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255) unique
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@sina.com');
--ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
两个字段联合起来具有唯一性
create table t_vip(
id int,
name varchar(255),
email varchar(255),
--约束没有添加在列的后面，这种约束被称为表级约束
unique(name,email)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
--ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
主键约束primary key
主键的相关术语
- 主键约束：就是一种约束
- 主键字段：该字段上添加了主键约束，这样的字段叫做主键字段
- 主键值：主键字段中的每一个值都叫做主键值
主键的特征：not null + unique（主键值不能是NULL同时也不能重复）
- 主键值是每一行记录的唯一标识 (身份证号) , 主键值一般都是数字，一般都是定长的, 建议使用：int , bigint , char等类型 , 不建议使用：varchar来做主键
- 任何一张表都应该有主键，没有主键则表无效
主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的 , 实际开发中建议使用单一主键
- 因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到
- 复合主键比较复杂，不建议使用
主键除了单一主键和复合主键之外，还可以分为自然主键和业务主键
- 自然主键：主键值是一个自然数，和业务没关系
- 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键
在实际开发中尽量使用自然主键，因为主键只要做到不重复就行，不需要有意义 , 业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用
一张表主键约束只能有一个 , 但是这个主键可以是单一主键也可以是复合主键
- 复合主键是给多个字段联合添加一个主键约束, 本质就是一个主键
--ERROR 1068 (42000): Multiple primary key defined
create table t_vip(
id int primary key,
name varchar(255) primary key
);
使用列级约束添加单一主键
--1个字段做主键，叫做：单一主键
--使用列级约束添加主键
create table t_vip(
--表示id列是主键
id int primary key,
name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
--错误不能重复: ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
insert into t_vip(id,name) values(2,'wangwu');
--错误不能为NULL: ERROR 1364 (HY000): Field 'id' doesn't have a default value
insert into t_vip(name) values('zhaoliu');
使用表级约束添加单一主键
--使用表级约束添加单一主键
create table t_vip(
id int,
name varchar(255),
primary key(id)
);
使用表级约束添加复合主键
--使用表级约束给多个字段联合起来添加主键约束: 复合主键
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
--错误不能重复: ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
auto_increment
在MySql当中的自动维护一个主键值的机制: auto_increment表示自增 , 从1开始，以1递增
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zvVS1meQ-1676261205992)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666963230079.png)]
drop table if exists t_vip;
create table t_vip(
--auto_increment表示自增，从1开始，以1递增
id int primary key auto_increment,
name varchar(255)
);
--添加自增长的字段的三种方式
insert into t_vip(id,name) values(null,'zhangsan');
--推荐使用这种方式
insert into t_vip(name) values('zhangsan');
insert into t_vip values(null,'zhangsan');
-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100
外键约束
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于所参照的表的主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N8RkMB8f-1676261205993)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666963659328.png)]
外键约束涉及到的相关术语
- 外键约束：一种约束（foreign key）
- 外键字段：该字段上添加了外键约束
- 外键值：外键字段当中的每一个值
设计数据库表，来描述“班级和学生”的信息
第一种方案：班级和学生存储在一张表中
- 缺点数据冗余，空间浪费
t_student
no(pk) name classno classname
----------------------------------------------------------------------------------
1 jack 100 北京市大兴区亦庄镇第二中学高三1班
2 lucy 100 北京市大兴区亦庄镇第二中学高三1班
3 lilei 100 北京市大兴区亦庄镇第二中学高三1班
4 hanmeimei 100 北京市大兴区亦庄镇第二中学高三1班
5 zhangsan 101 北京市大兴区亦庄镇第二中学高三2班
6 lisi 101 北京市大兴区亦庄镇第二中学高三2班
7 wangwu 101 北京市大兴区亦庄镇第二中学高三2班
8 zhaoliu 101 北京市大兴区亦庄镇第二中学高三2班
第二种方案：班级一张表、学生一张表
- 子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束 , 外键值可以为NULL
--t_class 主表(班级表)
classno(pk) classname
------------------------------------------------------
100 北京市大兴区亦庄镇第二中学高三1班
101 北京市大兴区亦庄镇第二中学高三1班
--t_student 从表(学生表)
--当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在,所以为了保证cno字段中的值都是100和101，需要给cno字 段添加外键约束,那么：cno字段就是外键字段。cno字段中的每一个值都是外键值
no(pk) name cno(FK引用t_class这张表的classno)
----------------------------------------------------------------
1 jack 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 zhaoliu 101
建立学生和班级表之间的连接
一旦建立主外键的关系，数据不能随意删除了 , 除非先把从表中的引用删除掉才能删掉主表的数据
--首先建立班级表 t_classes
drop table if exists t_classes;
create table t_classes(
classes_id int(3) primary key,
classes_name varchar(40),
)
--建立t_student 并加入外键约束
drop table if exists t_student;
create table t_student(
student_id int(10) primary key auto_increment,
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
--外键值可以为NULL,成功的插入了学生信息，但是 classes_id 没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空
--不确定外键的情况下可以使用NULL
classes_id int(3) not null,
foreign key(classes_id) references t_classes(classes_id)
--constraint student_id_pk primary key(student_id),
--constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)
--向 t_student 中加入数据
--出现错误，因为在班级表中不存在班级编号为 10 班级，外键约束起到了作用存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
--外键值可以为NULL,成功的插入了学生信息，但是 classes_id 没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', NULL)
添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误
t_classes是父表 , t_student是子表
- 删除表的顺序: 先删子，再删父
- 创建表的顺序: 先创建父，再创建子
- 删除数据的顺序: 先删子，再删父
- 插入数据的顺序: 先插入父，再插入子
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WfVErX24-1676261205993)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666948933836.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s2HUlGMT-1676261205994)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666949007518.png)]
--插入数据的顺序: 先插入父，再插入子
insert into t_classes (classes_id,classes_name) values (10,'366');
insert into t_student(student_id, student_name, sex, birthday, email, classes_id)
values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据
update t_classes set classes_id = 20 where classes_name = '366';
delete from t_classes where classes_id = 10;
因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以
先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，
再删除父表中的数据，采用 drop table 也不行，必须先 drop 子表，再 drop 父表
check约束
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2E92Zf5u-1676261205994)(C:\Users\meng\AppData\Roaming\Typora\typora-user-images\1666964458222.png)]
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);
--mysql5.7目前还不支持check,只做语法校验所以约束不会生效,数据依旧能插入表中
INSERT INTO t23
VALUES(1, 'jack', 'mid', 1);
5396
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
