【MySQL】（七）SQL约束——主键约束、非空约束、唯一约束、默认值约束、外键约束

已于 2022-11-25 23:09:15 修改
阅读量5.3k 收藏 76
点赞数 8
分类专栏： 数据库 文章标签： sql mysql 数据库 SQL约束
于 2022-08-19 13:39:42 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gjj920318/article/details/126421173
版权

目录

前言：
本文为在霍格沃兹测试开发学社中学习到的一些技术写出来分享给大家，希望有志同道合的小伙伴可以一起交流技术，一起进步~ 😘

所有SQL语法中符号含义：

  1. { }：必选项；
  2. [ ]：可选项；
  3. |：或，多选一

1. 主键约束

1.1 SQL 约束

  • 对表中的数据进行进一步的限制
  • 保证数据的正确性、有效性、完整性
  • 违反约束的不正确数据无法插入到表中
  • 约束是针对于字段有效
  • 常见的约束：
    • 主键：PRIMARY KEY
    • 非空：NOT NULL
    • 唯一：UNIQUE
    • 默认：DEFAULT
    • 外键：FOREIGN KEY

1.2 主键约束

  • 主键：一列（或一组列），其值能够唯一标识表中每一行
  • 特点：不可重复，唯一，非空
  • 语法：列名 字段类型 PRIMARY KEY

1.3 添加主键约束

-- 示例1：创建一个带主键的表
CREATE TABLE emp1(
    -- 设置主键 唯一 非空 
    eid INT PRIMARY KEY, 
    ename VARCHAR(20), 
    sex CHAR(1)
);

-- 示例2：给存在的表添加主键
CREATE TABLE emp2( 
    eid INT , 
    ename VARCHAR(20), 
    sex CHAR(1) 
)

----- 通过 DDL 语句进行设置 
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

-- 示例3：主键约束验证
--emp1中添加eid=1的数据：正常插入
INSERT INTO emp1(1,'张三','男');

--插入主键为NULL的数据：插入失败，报错
INSERT INTO emp1(NULL,'李四','男');

--插入主键值重复的数据：插入失败，报错
INSERT INTO emp1(1,'王二','男');

1.4 创建主键自增的表

-- 创建主键自增的表 
CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    ename VARCHAR(20), 
    sex CHAR(1) 
);

-- 查看新建表的详细信息
DESC emp3;

--添加数据，观察主键自动自增，
INSERT INTO emp3(ename,sex) VALUES('王二','男');  
INSERT INTO emp3(ename,sex) VALUES('李四','男'); 
INSERT INTO emp3(ename,sex) VALUES('红红','女');
INSERT INTO emp3(ename,sex) VALUES('绿绿','女');  

--DELETE 删除表数据然后再新增数据，观察自增值：自增编号由删除数据前主键值的最大值依次往下递增。对递增无影响，仍然按照之前的顺序依次增加
DELETE FROM emp4;
INSERT INTO emp4(ename,sex) VALUES('王二','男');   
INSERT INTO emp4(ename,sex) VALUES('李四','男'); 
INSERT INTO emp4(ename,sex) VALUES('红红','女');
INSERT INTO emp4(ename,sex) VALUES('绿绿','女'); 

--TRUNCAT DELETE 删除表数据然后再新增数据，观察自增值：自增编号由1开始依次递增。对递增有影响，TRUNCATE相当于表删掉再重新建一张相同的表
TRUNCATE TABLE  emp4;
INSERT INTO emp4(ename,sex) VALUES('王二','男');   
INSERT INTO emp4(ename,sex) VALUES('李四','男'); 
INSERT INTO emp4(ename,sex) VALUES('红红','女');
INSERT INTO emp4(ename,sex) VALUES('绿绿','女');
  • AUTO_INCREMENT：表示自动增长(字段类型必须是整数类型)

1.5 修改主键自增的起始值

-- 创建主键自增的表,自定义自增其实值 
CREATE TABLE emp4( 
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    ename VARCHAR(20), 
    sex CHAR(1) 
)AUTO_INCREMENT=100;

-- 查看新建表的详细信息
DESC emp4;

--添加数据，观察主键自动自增
INSERT INTO emp4(ename,sex) VALUES('王二','男');   
INSERT INTO emp4(ename,sex) VALUES('李四','男'); 
INSERT INTO emp4(ename,sex) VALUES('红红','女');
INSERT INTO emp4(ename,sex) VALUES('绿绿','女');
  • 在表定义的最后添加AUTO_INCREMENT的赋值

1.6 删除主键约束

-- 删除表中的主键语法
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 使用 DDL 语句删除表中的主键 
ALTER TABLE emp2 DROP PRIMARY KEY; 

-- 查看表结构
DESC emp2;

1.7 选择主键原则

  • 针对业务设计主键，建议每张表都设计一个主键
  • 主键可以没有业务意义，只需要保证不重复

2. 非空约束

2.1 非空约束

  • 非空约束特点: 某一列不予许为空
  • 语法：列名 字段类型 NOT NULL

2.2 添加非空约束

-- 添加非空约束
CREATE TABLE emp5( 
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    -- ename 字段不能为空 
    ename VARCHAR(20) NOT NULL, 
    sex CHAR(1) 
);

--查看表结构
DESC emp5;

--正常插入数据
INSERT INTO emp5(ename,sex) VALUES('王二','男');   

--插入ename为空的数据：插入失败，报错
INSERT INTO emp5(ename,sex) VALUES(NULL,'男');

3. 唯一约束

3.1 唯一约束

  • 唯一约束: 保证表中不是主键的某一列的值不能重复
  • 对 NULL 不做唯一的判断（唯一约束的列可以包含NULL值）
  • 语法：列名 字段类型 UNIQUE

3.2 添加唯一约束

-- 创建带有唯一约束的表  
CREATE TABLE emp6(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- 为 ename 字段添加唯一约束
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1) 
);

--查看表结构
DESC emp6;

--添加正常表数据
INSERT INTO emp6(ename,sex) VALUES('李四','男');   

--添加ename重复的数据：报错，ename重复
INSERT INTO emp6(ename,sex) VALUES('李四','女');

3.3 主键约束与唯一约束的区别

  1. 主键约束，唯一且不能够为空
  2. 唯一约束，唯一但是可以为空
  3. 一个表中只能有一个主键，但是可以有多个唯一约束

4. 默认值约束

4.1 默认值约束

  • 默认值约束：用来指定某列的默认值，如果列中不添加数据，则会添加默认值
  • 语法：列名 字段类型 DEFAULT 默认值

4.2 字段指定默认值

-- 创建带有默认值的表 
CREATE TABLE emp7( 
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20), 
    -- 为 sex 字段添加默认值 
    sex CHAR(1) DEFAULT '女'
);

--查看表结构
DESC emp7;

--插入使用默认值数据
INSERT INTO emp7(ename,sex) VALUES('青青',DEFAULT);  
INSERT INTO emp7(ename,sex) VALUES('兰兰'); 

--插入非默认值数据 
INSERT INTO emp7(ename,sex) VALUES('小李','男');

5. 多表简介

5.1 多表及使用场景介绍

  • 多表顾名思义就是在数据库设计中使用多张表格来实现数据存储的要求
  • 在实际的项目开发中，数据量大而且复杂，需要分库分表
  • 分表：按照一定的规则，对原有的数据库和表进行拆分
  • 表与表之间可以通过外键建立连接

5.2 多表设计案例

假定我们现在需要创建一张员工信息表，包含字段：

  • eid 员工ID (自增主键)
  • ename 员工姓名
  • age 年龄
  • gender 性别
  • dept_name 所在部门
  • dept_id 部门ID
  • dept_manager 部门主管
  • dept_location 所在地点

5.2.1 以单表的形式完成建表

创建员工信息表：

CREATE TABLE emp(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),  
dept_name VARCHAR(20),  
dept_id INT,  
dept_manager VARCHAR(20),  
dept_location VARCHAR(20)  
);

插入数据：

INSERT INTO emp VALUES (1,'张三', 20, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location)
VALUES ('李四', 25, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('宋江', 40, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('林冲', 25, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('林徽因', 25, '女','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('周芷若', 25, '女','运营部',2,'赵敏','深圳');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('任盈盈', 25, '女','运营部',2,'赵敏','深圳');

单表数据冗余：
在这里插入图片描述

5.2.2 多表设计模式

  • 将数据拆分为员工信息表employee和部门信息表dept
  • 两个表之间通过部门id：dept_id字段连接

创建表：

# 创建员工信息表
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT
);

# 创建部门表
CREATE TABLE dept(  
id INT PRIMARY KEY AUTO_INCREMENT,  
dept_name VARCHAR(20),  
dept_manager VARCHAR(20),  
dept_location VARCHAR(20)  
);

插入数据：

# 向部门表插入数据
INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('研发部','张无忌','北京');
INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('运营部','赵敏','深圳');

# 向员工信息表插入数据
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('李四', 25, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('宋江', 40, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('张三', 20, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林冲', 25, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林徽因', 25,'女',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('周芷若', 25,'女',2);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('任盈盈', 25, '女',2);

多表关系：
在这里插入图片描述

5.3 使用多表的优点

  • 简化数据
  • 提高复用性
  • 方便权限控制
  • 提高系统的稳定性和负载能力

6. 外键约束

6.1 外键约束的定义与意义

  • 主键：可以唯一标识一条记录的列
  • 外键：从表中与主表的主键对应的字段
  • 主表：外键所指向的表,约束其他表的表
  • 从表：外键所在的表，被约束的表
  • 价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

在这里插入图片描述

6.2 建立外键约束

  • 创建表时添加外键约束：
CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]
  • 添加外键约束：
ALTER TABLE [表名] ADD CONSTRAINT [外键约束名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]

示例：

--创建一个关联到主表的从表
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
);

--给已有从表添加外键约束，如果从表中有非法数据，则没办法添加外键约束
ALTER TABLE emp_part ADD CONSTRAINT enp_dept FOREIGN KEY [dept_id] REFERENCES dept(id);

--插入一条非法数据：报错。 注意：当在从表要插入的数据中外键值在主表中无法查找，应先在主表中插入相对应的主键值的数据，然后再插入从表中数据
INSERT INTO emp_part VALUES(1,'cindy',20,'F,'4');

--注意：当在从表要插入的数据中外键值在主表中无法查找，应先在主表中插入相对应的主键值的数据，然后再插入从表中数据

6.3 删除外键约束

  • 语法：
ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]
  • 注意事项：
    • 从表外键数据类型必须与主表的主键一致
    • 删除数据时，需先删除从表数据再删除主表的数据
    • 添加数据时先添加主表数据，再添加从表数据
--删除外键约束 
ALTER TABLE emp_part DROP FOREIGN KEY emp_dept 

--插入一条非法数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','4') 

SELECT * FROM emp_part  

---向主表中插入一条数据
INSERT INTO dept VALUES(2,'运营部','张三','北京') 

--向从表中插入一条数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','2') 

---删除主表中的数据:报错，原因是有一条外键约束
DELETE FROM dept WHERE id=2

--注意：需要先删除从表中的数据，再删除主表中的数据

6.4 级联删除

  • 删除主表数据的同时,也删除掉从表中相关的数据：
  • 创建从表时进行级联删除设置：ON DELETE CASCADE
--创建一个关联到主表的从表，并设置级联删除
CREATE TABLE emp_part(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
gender VARCHAR(10), dept_id INT, 
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id) 
-- 设置允许级联删除 
ON DELETE CASCADE 
); 

--给已有从表添加级联删除约束
ALTER TABLE emp_part ADD CONSTRAINT enp_dept FOREIGN KEY [dept_id] REFERENCES dept(id) ON DELETE CASCADE;

--向员工信息表中添加一条数据 
INSERT INTO emp_part VALUES(1,‘cindy’,20,‘female’,‘2’) ;

--删除主表中部门id=2的部门 
DELETE FROM dept WHERE id=2 ;

--查看从表中的数据是否同时被删除 
SELECT * FROM emp_part ;

文末说明：
接口测试中我们很容易混淆Session、cookie和token，你知道他们有什么区别吗？

快来跟我一起看，一篇文章让你了解三者的区别。😎
⬇⬇⬇⬇⬇⬇⬇
👍👍👍：接口测试经典面试题：Session、cookie、token有什么区别？

爱测试的高胖胖
关注
  • 8
    点赞
  • 踩
  • 76
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
（mysql学习笔记9）约束（非空约束、唯一约束、主键约束、外键约束）
qq_43791665的博客
04-18 762
约束 什么是约束，常见的约束有哪些 在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。 常见的约束有： 非空约束（not null）：约束的字段不能为null 唯一约束（unique）：约束的字段不能重复 主键约束（primary key）：约束的字段不能为null也不能重复（简称PK） 外键约束（foreign key）：···（简称FK） 检查约束（check）：注意Oracle数据库有check约束，当时mysql没有，目前mysq
MySQL非空约束
weixin_45743799的博客
03-13 7294
MySQL 非空约束（NOT NULL）可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空。 非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。 在创建表时设置非空约束 创建表时可以使用 NO...
SQL的主键和外键约束
weixin_30466421的博客
04-23 1654
SQL的主键和外键约束 SQL的主键和外键的作用： 外键取值规则：空值或参照的主键值。 (1)插入非空值时，如果主键表中没有这个值，则不能插入。 (2)更新时，不能改为主键表中没有的值。 (3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。 (4)更新主键记录时，同样有级联更新和拒绝执行的选择。 简而言之，SQL的主键和外键就是起约束作用。 ...
sql server 中的6种约束
最新发布
zgscwxd的博客
05-27 1113
sql server 中的6种约束
sql在已经创建好的表中插入主键和外键约束
weixin_70217492的博客
05-14 2614
对于初学者来说sql语句太多，设计表时心中没有一个概念，创建好表中忘记设置主键和外键，为此花费很多时间去查找插入的主外键的语句。 语句如下： 外键插入语句 alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名); 主键插入语句 ALTER TABLE 表名 MODIFY id INT PRIMARY KEY AUTO_INCREMENT;（PRIMARY...
mysql添加非空约束_如何在MySQL中向现有列添加非空约束
weixin_42399813的博客
01-19 2148
I have table name called "Person" with following column namesP_Id(int),LastName(varchar),FirstName (varchar).I forgot to give NOT NULL Constraint to P_Id.Now I tried with following query to add NOT NU...
对于现在已有表 设置主键，非空和自增长的 语句
一条努力翻身的金龙鱼
06-25 1176
ALTER TABLE ai_report_date MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY (id);
MySQL约束（主键，唯一，非空，外键）
12-14
这些约束包括主键约束、唯一约束、非空约束和外键约束。 1. **非空约束（NOT NULL）** - 非空约束用于强制指定字段的值不能为NULL。这意味着当向表中插入数据时，该字段必须包含有效值。 - 创建表时添加非空约束...
MySQL外键约束的禁用与启用命令
09-09
在MySQL数据库系统中，外键约束（Foreign Key Constraints）是一种重要的数据完整性机制，它确保了表之间的参照完整性，即一个表中的数据只能引用另一个表中存在的数据。外键约束可以防止非法数据的插入，保证数据库...
详解MySQL 外键约束
09-08
MySQL中的外键约束是数据库设计中的一个重要概念，用于维护数据库中的引用完整性，确保表与表之间的数据关联正确无误。本文将深入探讨外键的作用、使用条件、创建语法以及通过案例来演示其实际应用。 首先，外键的...
详解MySQL中的外键约束问题
09-10
MySQL中的外键约束是数据库设计中用于维护数据完整性和参照完整性的关键机制，特别是在关系数据库中。外键约束确保了在一个表中的数据（子表）与另一个表（父表）中的数据之间的关联性。在MySQL中，外键约束主要用于...
MySQL 关闭子表的外键约束检察方法
09-09
在MySQL数据库中，外键约束是确保数据完整性和一致性的重要机制。它允许子表引用主表中的记录，确保引用的外键值存在于主表中。然而，在某些特殊情况下，我们可能需要暂时关闭这个约束，例如在批量导入数据或进行...
SQL Server向数据库表中添加主键列
null
02-28 1万+
SQL 目录：https://blog.csdn.net/dkbnull/article/details/87932858 SQL Server 数据库，向已设置主键的数据库表中插入新一列，并设为主键。 首先从基础知识开始看， 建表： create table 表名 ( 字段名1 int not null, …………, ...
sql的外键约束和主键约束_SQL约束
cumtb2002的博客
08-02 778
sql约束 SQL | 约束条件 (SQL | Constraints) Constraints are the guidelines implemented on the information sections of a table. These are utilized to restrict the kind of information that can go into a table....
主键和外键约束
weixin_33859504的博客
05-05 199
SQL的主键和外键的作用： 外键取值规则：空值或参照的主键值。 (1)插入非空值时，如果主键表中没有这个值，则不能插入。 (2)更新时，不能改为主键表中没有的值。 (3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。 (4)更新主键记录时，同样有级联更新和拒绝执行的选择。 简而言之，SQL的主键和外键就是起约束作用。 定义主键和外键主要是为...
SQL SERVER添加与删除主键约束
dengguawei0519的博客
02-01 992
PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。主键约束操作包含了添加约束和删除约束，修改约束其实是添加约束或者删除约束。添加主键约束比较 简单，但是删除一个没有约束名的主键约束则比较复杂，如果不是很不了解SQL Server的话则比很难实现该功能。 主键约束操作基本语句 ...
oracle对表添加主键约束的sql语句
热门推荐
看不过的黑工坊
02-27 1万+
转自：http://blog.csdn.net/u014455929/article/details/52374523 –创建主外键（可以在创建表的同时添加主外键约束，也可以  –创建完成后再添加约束 ） —–方法一：表创建的同时，添加主键约束 语法： create table 表名  (  列名1 数据类型及长度 constraint 主键名称(一般主键名称为”
Oracle:SQL语句--对表的操作——添加主键约束
aihui8050的博客
08-31 1824
–创建主外键（可以在创建表的同时添加主外键约束，也可以 –创建完成后再添加约束 ） —–方法一：表创建的同时，添加主键约束 语法： create table 表名 ( 列名1 数据类型及长度 constraint 主键名称(一般主键名称为”PK_”开头) primary key, 列名2 数据类型及长度 not null,——-not null ...
sql server 唯一性约束 UNIQUE
08-11
在 SQL Server 中，可以使用唯一性约束 (UNIQUE constraint) 来确保表中的某一列或多列的值是唯一的。唯一性约束可以帮助我们在插入或更新数据时避免重复值的出现。 要在 SQL Server 中创建唯一性约束，可以使用以下语法： ```sql ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名1, 列名2, ...) ``` 其中，"表名" 是要添加唯一性约束的表的名称，"约束名" 是约束的名称（可选），"列名1, 列名2, ..." 是要添加唯一性约束的列的名称。 以下是一个示例，创建一个名为 "students" 的表，其中 "student_id" 列具有唯一性约束： ```sql CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(50), CONSTRAINT unique_student_id UNIQUE (student_id) ) ``` 在上述示例中，我们在 "students" 表中创建了一个名为 "unique_student_id" 的唯一性约束，它确保了 "student_id" 列中的值是唯一的。 当尝试插入或更新数据时，如果违反了唯一性约束，将会收到一个错误消息，无法进行操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值