【教程】MySQL数据库学习笔记（五）——约束（持续更新）

EliasChang

已于 2024-04-21 23:33:20 修改

阅读量1.3k

点赞数 28

分类专栏： # SQL 文章标签：数据库 mysql 学习

于 2024-04-21 23:32:45 首次发布

本文链接：https://blog.csdn.net/eliaschang/article/details/138043679

版权

SQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

文首标志
写在前面：
如果文章对你有帮助，记得点赞关注加收藏一波，利于以后需要的时候复习，多谢支持！

【MySQL数据库学习】系列文章

第一章《认识与环境搭建》
第二章《数据类型》
第三章《数据定义语言DDL》
第四章《数据操作语言DML》
第五章《约束》

文章目录

【MySQL数据库学习】系列文章

一、约束介绍

约束（Constraints） 是用于强制数据库中数据完整性和一致性的规则。它们定义了对表中数据的限制，确保数据的有效性和正确性，实际上就是表中数据的限制条件。例如，用户表的有些列（比如手机号）不能为空，有些列的值（比如身份证）不能重复等规则就是添加的约束条件。

约束主要的分类有以下常见类别：

数据完整性约束
- 主键约束（Primary Key Constraint）：
  确保每行数据在指定列或列组合上具有唯一标识，并且不允许包含NULL值。
- 外键约束（Foreign Key Constraint）：
  确保表中的外键列的值必须存在于另一个表的主键列中，保持引用完整性。
- 唯一约束（Unique Constraint）：
  确保指定列中的所有值都是唯一的，但允许包含NULL值。
- 检查约束（Check Constraint）：
  用于确保特定列中的值满足指定的条件，例如范围、格式等，以确保数据的一致性。
- 自增长约束（Auto Increment Constraint）：
  确保在插入新记录时，列中的值自动按顺序递增。
数据格式约束
- 默认约束（Default Constraint）：
  用于确保特定列中的值满足指定的条件，例如范围、格式等，以确保数据的一致性。
- 零填充约束（Zero Padding Constraint）：
  确保列中的值采用零填充以达到特定长度的要求。
空值约束
- 非空约束（Not Null Constraint）：
  用于确保特定列中的值满足指定的条件，例如范围、格式等，以确保数据的一致性。

常用的约束有主键约束、自增长约束、非空约束、唯一约束、默认约束等。

二、常用约束

（一）主键约束

主键约束（Primary Key Constraint） 是一个列或多个列的组合，其值能唯一地标识表中的每一行，方便在关系型数据库中尽快地找到特定的某一行数据。主键约束相当于唯一约束+非空约束，即主键约束列不允许重复，也不允许出现空值（NULL值），创建主键约束时，系统会默认在所在列和列组合上建立对应的唯一索引。

需要注意的是每个表只能有一个主键，此主键可以是一列，也可以是多列的组合（只要保证组合唯一即可）。关键字是PRIMARY KEY。

关于主键约束的操作有以下几种：

添加单列主键
添加多列联合主键
删除主键

1.添加单列主键

创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段后再指定主键。

第一种方式的语法如下。

CREATE TABLE 表名 (
	...
	<字段名> <数据类型> PRIMARY KEY
	...
);

下面是第一种方式的一个简单的示例。

USE mydb1;
-- 第一种方式
CREATE TABLE IF NOT EXISTS emp1 (
    eid INT PRIMARY KEY,
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE
);

此时就可以设置eid列为主键列。

第二种方式的语法如下。

CREATE TABLE 表名 (
	...
	[CONSTRAINT <约束名>] PRIMARY KEY (字段名)
);

约束名由于不强制添加，不填写约束名也可以。

下面是第二种方式的一个简单的示例。

-- 第二种方式
CREATE TABLE IF NOT EXISTS emp2 (
    eid INT,
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE,
	CONSTRAINT pk1 PRIMARY KEY(eid) -- CONSTRAINT pk1可以省略
);

主键约束已经添加完毕了，具体作用可以通过一个示例验证。

INSERT INTO emp2 VALUES(1001,'John',10,5000);
INSERT INTO emp2 VALUES(1001,'Mike',20,6000);

此时会返回错误如下。

INSERT INTO emp2 VALUES(1001,'John',10,5000)
> 1062 - Duplicate entry '1001' for key 'emp2.PRIMARY'
> 查询时间: 0s

这就说明，对于已经设置主键约束的列的值不能重复。

INSERT INTO emp2 VALUES(NULL,'Mike',20,6000);

而如果将添加的一行数据中eid值设置为空值，也会报错。

INSERT INTO emp2 VALUES(NULL,'Mike',20,6000)
> 1048 - Column 'eid' cannot be null
> 查询时间: 0s

这就说明，对于已经设置主键约束的列的值也不能为空。

2.添加联合主键

所谓的联合主键，就是指主键由多个字段组成，而此时就无法在某个字段定义同时指定主键了，语法如下。

CREATE TABLE 表名 (
	...
	[CONSTRAINT <约束名>] PRIMARY KEY (字段1,字段2,...,字段n)
);

下面是一个简单的示例。

CREATE TABLE IF NOT EXISTS emp3 (
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE,
	PRIMARY KEY(name,deptID)
);

此例去除了eid，转而将姓名name和部门IDdeptID作为列组合设置主键约束。同单列主键一样，联合主键的每一个列都受到主键约束，即非空且唯一。

3.修改表结构添加主键

除了以上通过CREATE TABLE关键字设置主键约束的方法，指定主键也可以通过修改表结构来实现，语法如下。

CREATE TABLE 表名 (
    ...
);
ALTER TABLE 表名 ADD PRIMARY KEY(指定字段1,指定字段2,,...,指定字段n);

下面是一个简单的示例。

CREATE TABLE IF NOT EXISTS emp4 (
    eid INT,
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE
);
ALTER TABLE emp4 ADD PRIMARY KEY(eid);

4.删除主键

一个表中不再需要主键约束时，就可以将主键约束删除，而这通过修改表结构来实现，语法如下。

ALTER TABLE <表名> DROP PRIMARY KEY;

可以看到，无论是删除单列主键还是联合主键，都不需要指定主键名，由于表中只允许出现一个主键，所以使用删除语句就会自动指定唯一主键进行删除。

（二）自增长约束

在MySQL中，当主键定义为自增长后，这个值就不需要用户输入数据了，而是数据库根据定义直接自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。这可以通过给字段添加AUTO_INCREMENT属性来实现主键自增长，语法如下。

<字段名> <数据类型> PRIMARY KEY AUTO_INCREMENT

下面是一个简单的示例。

CREATE TABLE IF NOT EXISTS user1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);
INSERT INTO user1 VALUES(NULL,'John');
INSERT INTO user1(name) VALUES('Mike');

可以看到，此例插入的数据并没有指定id字段的值，但查看表会发现此字段被自动升序赋值数字。
在这里插入图片描述
需要注意的是，自增长约束只能增加到主键上，并且数据类型只能是整型。

而如果想要更改自增长开始的数字，则可以在示例的基础上增加一些语句，在创建表的时候设置如下。

CREATE TABLE IF NOT EXISTS user1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
)AUTO_INCREMENT=100; -- 设置开始数字从100开始

通过修改表结构实现如下。

CREATE TABLE IF NOT EXISTS user1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);
ALTER TABLE user1 AUTO_INCREMENT=100; -- 设置开始数字从100开始

需要注意的是，当使用DELETE或TRUNCATE删除数据后，自增长的起点是不同的：如果使用DELETE删除了数据，增长后会在断点后继续增长；而如果使用TRUNCATE则默认从初始值开始增长。

（三）非空约束

非空约束（Not Null Constraint） 是指令字段的值不能为空的约束。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，系统就会报错，语法如下。

<字段名> <数据类型> NOT NULL; -- 方式1：在创建表时指定
ALTER TABLE <表名> MODIFY <字段名> <数据类型> NOT NULL; -- 方式2：通过修改表结构指定

以下是一个简单的示例。

CREATE TABLE IF NOT EXISTS emp1 (
    eid INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    deptID INT NOT NULL,
    salary DOUBLE
);

或者使用方式2。

CREATE TABLE IF NOT EXISTS emp1 (
    eid INT PRIMARY KEY,
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE
);
ALTER TABLE emp1 MODIFY name VARCHAR(20) NOT NULL;
ALTER TABLE emp1 MODIFY deptID INT NOT NULL;

可以看到非空约束并不像主键约束一样只能指定一个，而是可以指定多个。

而如果想要删除以上添加的约束则使用如下语句。

ALTER TABLE emp1 MODIFY name VARCHAR(20);
ALTER TABLE emp1 MODIFY deptID INT;

可以看到，其实“删除”并非是真的通过某种语句删除了非空约束，而是通过MODIFY关键字重新修订表结构来去除之前指定的约束以实现“删除”的效果。

（四）唯一约束

**唯一约束（Unique Constraint）**是指所有记录中的值不能重复出现，否则报错，其语法如下。

<字段名> <数据类型> UNIQUE; -- 方式1：在创建表时指定
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列); -- 方式2：通过修改表结构指定

下面是方法一的一个简单的示例。

CREATE TABLE IF NOT EXISTS emp1 (
    eid INT UNIQUE,
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE
);

可以看到，这里对于eid列指定了唯一约束，这也可以通过修改表结构来实现。

CREATE TABLE IF NOT EXISTS emp1 (
    eid INT,
    name VARCHAR(20),
    deptID INT,
    salary DOUBLE
);
ALTER TABLE emp1 ADD CONSTRAINT u1 UNIQUE(eid);

而删除此约束的语法如下。

ALTER TABLE 表名 DROP INDEX 约束名或列名;

这里需要注意的是如果赋予约束名则可以通过约束名定位到约束，而如果像第一种方式在创建表的时候指定了约束，并没有约束名时，字段名（列名）也可以定位到约束。

（五）默认约束

默认约束（Default Constraint） 可以用来指定某列的默认值，语法如下。

<字段名> <数据类型> DEFAULT <默认值>; -- 方式1：在创建表时指定
ALTER TABLE <表名> MODIFY <字段名> <数据类型> DEFAULT <默认值>; -- 方式2：通过修改表结构指定
ALTER TABLE <表名> MODIFY <字段名> <数据类型>; -- 删除方法