MySQL（十五）—— 创建表加入约束

最新推荐文章于 2023-09-20 18:33:52 发布

大彤小忆

最新推荐文章于 2023-09-20 18:33:52 发布

阅读量1.6k

点赞数

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

本文链接：https://blog.csdn.net/HUAI_BI_TONG/article/details/119841084

版权

MySQL 专栏收录该内容

20 篇文章 7 订阅

订阅专栏

MySQL数据库系列内容的学习目录 $\rightarrow$ 老杜带你学MySQL学习系列内容汇总。

15. 创建表加入约束

15. 创建表加入约束

15.1 什么是约束

约束对应的英语单词：constraint。
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！
约束的作用就是为了保证表中的数据有效！

15.2 约束包括哪些

非空约束：not null
唯一性约束：unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

15.3 非空约束 not null

非空约束not null：约束的字段不能为NULL。

创建一个表 t_vip：

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null
);

not null只有列级约束，没有表级约束！

插入数据：

insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

查看表中数据：

select * from t_vip;

在这里插入图片描述
当只插入id信息，name信息为NULL时，会报错：

insert into t_vip(id) values(3);

在这里插入图片描述
也可以批量执行sql语句，新建一个vip.sql的文件，将所有sql语句复制进来并保存。在命令行输入source 文件路径，可以批量执行sql语句。

在这里插入图片描述
注意： 1. xxxx.sql 这种文件被称为sql脚本文件。
2. sql 脚本文件中编写了大量的 sql 语句。我们执行sql脚本文件的时候，该文件中所有的 sql 语句会全部执行！
3. 批量的执行 sql 语句，可以使用 sql 脚本文件。
4. 在mysql当中执行sql脚本：source E:\工具+源码+资料\document\vip.sql

15.4 唯一性约束 unique

唯一性约束 unique ：约束的字段不能重复，但是可以为NULL。

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');
select * from t_vip;

在这里插入图片描述
当再插入的数据与之前的有重复时，会报错：

insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');

在这里插入图片描述
再插入两行数据，可以为NULL：

insert into t_vip(id) values(4);
insert into t_vip(id) values(5);

在这里插入图片描述
name字段虽然被unique约束了，但是可以为NULL。

新需求： name和email两个字段联合起来具有唯一性！

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) unique, 
	email varchar(255) unique
);

name varchar(255) unique，约束直接添加到列后面的，叫做列级约束。这张表这样创建是不符合我以上“新需求”的。
这样创建表示：name具有唯一性，email具有唯一性，各自唯一。
以下插入的数据是符合name和email两个字段联合起来具有唯一性的需求的，但如果采用上述方式创建表的话，肯定创建失败，因为’zhangsan’和’zhangsan’重复了。

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');

在这里插入图片描述
怎么创建表，才能符合新需求呢？

drop table if exists t_vip;
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');
select * from t_vip;

unique(name,email) ，约束没有添加在列的后面，这种约束被称为表级约束。
上述方式创建表，再插入数据可以使得name和email两个字段联合起来唯一！

在这里插入图片描述
再插入一行name和email均重复的数据，会报错（name和email联合起来重复会报错，但是name或者email其中任何一个重复是可以的）：

insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');

在这里插入图片描述
什么时候使用表级约束呢？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

unique 和not null可以联合吗？ 可以。

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null unique
);
desc t_vip;

在这里插入图片描述

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

插入一行数据：insert into t_vip(id,name) values(1,'zhangsan');

在这里插入图片描述
再插入一行name重复的数据：insert into t_vip(id,name) values(2,'zhangsan'); ，会报错。

在这里插入图片描述
再插入一行name为NUL的数据：insert into t_vip(id) values(2); ，会报错。

在这里插入图片描述

15.5 主键约束（primary key）

主键约束： primary key，简称PK。
主键约束的相关术语：
$\diamond$ 主键约束： 就是一种约束。
$\diamond$ 主键字段： 该字段上添加了主键约束，这样的字段叫做主键字段
$\diamond$ 主键值： 主键字段中的每一个值都叫做主键值。

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的。

什么是主键？有什么作用？
主键值是每一行记录的唯一标识。主键值是每一行记录的身份证号！
记住：任何一张表都应该有主键，没有主键，表无效！
主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

怎么给一张表添加主键约束呢？

drop table if exists t_vip;
create table t_vip(
	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');

其中，id int primary key为列级约束。
上述语句中，id做主键：单一主键！

在这里插入图片描述
再插入一行主键id重复的数据：insert into t_vip(id,name) values(2,'wangwu');，会报错。

在这里插入图片描述
再插入一行主键id为NULL的数据：insert into t_vip(name) values('zhaoliu');，会报错。

在这里插入图片描述
可以如下所示这样添加主键吗，使用表级约束？

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255),
	primary key(id) 
);
insert into t_vip(id,name) values(1,'zhangsan');

其中，primary key(id)为表级约束。

在这里插入图片描述
再插入一行主键id重复的数据：insert into t_vip(id,name) values(1,'lisi');，会报错。

在这里插入图片描述
表级约束主要是给多个字段联合起来添加约束？

drop table if exists t_vip;
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');

上述语句中，id和name联合起来做主键：复合主键！

在这里插入图片描述
再插入一行主键id和name均重复的数据：insert into t_vip(id,name,email) values (1, 'lisi', 'lisi@123.com');，会报错。

在这里插入图片描述
在实际开发中不建议使用复合主键，建议使用单一主键！因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。而复合主键比较复杂，不建议使用。

一个表中主键约束能加两个吗？ 不可以。

drop table if exists t_vip;
create table t_vip(
	id int primary key,
	name varchar(255) primary key
);

在这里插入图片描述
结论： 一张表，主键约束只能添加1个（主键只能有1个）。

主键值建议使用int、bigint、char等类型，不建议使用varchar来做主键。主键值一般都是数字，一般都是定长的。

主键分类：单一主键和复合主键
$\diamond$ 单一主键：由一个字段构成。
$\diamond$ 复合主键：由多个字段构成。
自然主键和业务主键
$\diamond$ 自然主键：主键值是一个自然数，和业务没关系。
$\diamond$ 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。

在实际开发中使用自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用，尽量使用自然主键。

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值。

drop table if exists t_vip;
create table t_vip(
	id int primary key auto_increment, 
	name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;

其中，id int primary key auto_increment语句中auto_increment表示自增，从1开始，以1递增。

在这里插入图片描述

15.6 外键约束（foreign key）

外键约束： foreign key，简称FK。
外键约束的相关术语：
$\diamond$ 外键约束： 一种约束（foreign key）。
$\diamond$ 外键字段： 该字段上添加了外键约束。
$\diamond$ 外键值： 外键字段当中的每一个值。

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必
须来源于参照的表的主键，如：emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。

业务背景： 请设计数据库表，来描述“班级和学生”的信息。

第一种方案：班级和学生存储在一张表中
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班

以上方案的缺点：数据冗余，空间浪费！

第二种方案：班级一张表、学生一张表
t_class 班级表

 classno(pk)			classname
 ------------------------------------------------------
 100				    北京市大兴区亦庄镇第二中学高三1班
 101				    北京市大兴区亦庄镇第二中学高三1班

t_student 学生表

	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

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束，那么cno字段就是外键字段，cno字段中的每一个值都是外键值。

首先建立班级表 t_classes：

drop table if exists t_classes;
create table t_classes(
    classes_id int(3),
    classes_name varchar(40),
    constraint pk_classes_id primary key(classes_id)
);

在 t_student 中加入外键约束：

drop table if exists t_student;
create table t_student(
    student_id int(10),
    student_name varchar(20),
    sex char(2),
    birthday date,
    email varchar(30),
    classes_id int(3),
    constraint student_id_pk primary key(student_id),
    constraint fk_classes_id foreign key(classes_id) references      t_classes(classes_id) 
);

其中，constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)语句中加入了外键约束。

向 t_student 中加入数据：

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);

在这里插入图片描述
出现错误，因为在班级表中不存在班级编号为 10 班级，外键约束起到了作用存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表。将classes_id改为null，就可以成功插入数据了。

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_class 是父表
t_student是子表

删除表的顺序：先删子，再删父。
创建表的顺序：先创建父，再创建子。
删除数据的顺序：先删子，再删父。
插入数据的顺序：先插入父，再插入子。

思考： 子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有unique约束。

测试： 外键可以为NULL吗？
外键值可以为NULL。

大彤小忆

关注

0
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
MySQL（十五）—— 创建表加入约束

15. 创建表加入约束15.1 什么是约束15.2 约束包括哪些15.3 非空约束 not null15.4 唯一性约束 unique15. 5 主键约束（primary key）15.6 外键约束（foreign key）15. 创建表加入约束15.1 什么是约束约束对应的英语单词：constraint。在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！约束的作用就是为了保证表中的数据有效！15.2 约束包括哪些非空约束：not null.
复制链接

扫一扫