MySQL基础篇-约束

目录

1.约束概述

 2.分类

3.测试user表的约束情况

主键约束

非空约束及唯一约束

检查约束

默认约束

4.外键约束

外键约束的语法

 外键约束的删除/更新行为

小结

---

1.约束概述

MySQL约束（Constraints）是用于确保表中数据完整性和一致性的规则。它们定义了数据库表中数据的限制条件，以确保数据满足特定的要求。

 2.分类

1. 主键约束（Primary Key Constraint）：主键是用于唯一标识表中每一行的列或列组合。主键列的值必须是唯一的，且不能为NULL。主键约束确保了表中的每一行都有一个唯一标识符。

2. 唯一约束（Unique Constraint）：唯一约束确保列或列组合中的值是唯一的，但允许NULL值。它用于防止在表中出现重复的数据。

3. 外键约束（Foreign Key Constraint）：外键用于建立表之间的关联关系。外键约束确保一个表中的值存在于另一个表的主键列中。这有助于维护表之间的引用完整性。

4. 检查约束（Check Constraint）：检查约束定义了列中允许的值的范围或条件。它可以用于强制数据的完整性，例如，确保年龄列中的值大于等于18。(版本要求是8.0.16之后)

5. 默认约束（Default Constraint）：默认约束定义了在插入新行时，如果未提供值，则将插入的默认值。这有助于确保表中的数据始终具有某些预定义的值。

6. 非空约束（NOT NULL Constraint）：非空约束确保列中的值不为空，即不能包含NULL值。

平常中的数据表，例如id 它是唯一标识，且不为空 ，并且一般情况下都是自己增长，对这些字段的限制进行一些约束，不满足条件的情况下是无法修改数据表中的数据的。

下面创建一个user表来测试

create table user(
    id int primary key auto_increment comment  '主键',
    name varchar(10) not null unique comment '昵称',
    age int  check ( age > 0 and age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表'

这个案例几乎把上面的六种约束都用到了，id有主键约束，其中的auto_increment是自增设置，name有非空约束和唯一约束，age则是检查约束，status则是默认约束。

3.测试user表的约束情况

主键约束

插入数据

insert into user(name, age, status, gender) values ('zs',19,'1','男'), ('ls',13,'0','女')

执行完后可以看到并没有插入id，但是因为我们使用的主键约束及自增处理，所以id会自增且会保持唯一性。

进一步验证唯一性可以执行下面的sql，先删除id为2的数据，在插入一个元素

delete from user where id = 2;
insert into user(name, age, status, gender) values ('ww',29,'1','男');

可以看到执行完后id=2的数据虽然呗删除了，但是因为它存在过，为了保持唯一性id会自增到3。

非空约束及唯一约束

按照上面的流程，我们再次插入name为‘ww’的元素，sql执行时会报错

insert into user(name, age, status, gender) values ('ww',29,'1','男');

 执行这样的sql，name字段为null或者不插入

insert into user(name, age, status, gender) values (null,29,'1','男');
insert into user( age, status, gender) values (20,'1','男')

会出现这样的错误

检查约束

 插入这样一条数据，age明显大于了我们要求的范围，此时也会出现对应的错误

insert into user(name, age, status, gender) values ('ll',121,'1','男');

默认约束

 执行这样的sql语句，不插入status，则会填入默认规定的 ‘1’

insert into user(name, age, gender) values ('pp',11,'男');

4.外键约束

 通过前面的分类大概就能知道了外键约束的作用，也就是通过某个字段将一张表与另外一张表关联起来，比如像下面的这种情况。

建表sql：

create table dept(
    id int auto_increment primary key,
    name varchar(40) not null comment '部门名称'
) comment '部门表';
insert into dept (name) values ('研发部'),('市场部'),('财务部');
 
 
create table spm(
    id int auto_increment primary key ,
    name varchar(50) not null ,
    age int,
    job varchar(40) comment  '职位',
    dept_id int comment '部门ID'
) comment '员工表';
 
insert into spm ( name, age, job, dept_id) values ('zs',20,'前端',1),('ls',30,'销售',2);

此时这里有一张员工表

以及一张部门表

员工表中的dept_id关联的是部门表，如果此时我们修改其中的数据，因为没有采用外键约束，他们相互之间是不受影响的，这只是一种理论上的约束关联，要想让他们真正的关联起来就要使用到外键约束。

外键约束的语法

 建表时给予外键约束

create table 表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
)

因为我在之前就已经建好了表，现在只能采用第二种方法

alter table spm add constraint fk_spm_dept_id foreign key (dept_id) references dept(id);

现在我们想删除部门表中的数据就会出现报错

此时已经建立了联系，为了保证数据的完整性，不能随意的删除数据了，此时就达到了 完整性，一致性的目的了。

删除关联

alter table spm drop foreign key fk_spm_dept_id;

 外键约束的删除/更新行为

外键约束的删除/更新行为主要目的还是为了维护数据的完整性和一致性，并确保表之间的关系得以维护。

常见的有以下这些

1. NO ACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致)。

2. SET NULL：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取null)’。

3. SET DEFAULT：父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)

4. RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTION一致)

5. CASCADE：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。

案例：

建立cascade连接

alter table spm add constraint fk_spm_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

此时建立连接后，只要修改父表中的数据则与之建立联系的表也会跟着修改，正如上面描述的那样

小结

1. 约束的作用：

   • 约束是一种用于确保数据库中数据的完整性和一致性的重要工具。
   • 它们定义了数据库表中数据的规则和限制，以防止无效或不一致的数据进入数据库。

2. 主要约束类型：

   • 主键约束（PRIMARY KEY）：用于唯一标识表中的每一行，确保每个值都是唯一的，同时不允许NULL值。
   • 唯一约束（UNIQUE）：确保列中的值是唯一的，但允许NULL值。
   • 外键约束（FOREIGN KEY）：用于建立表之间的关联关系，确保引用完整性，防止无效引用和维护数据一致性。
   • 检查约束（CHECK）：定义列中允许的值的范围或条件，确保数据满足特定规则。

3. 主键和唯一约束的区别：

   • 主键是一种用于唯一标识行的特殊唯一约束，同时不允许NULL值。
   • 唯一约束确保列中的值是唯一的，但允许一个NULL值。

4. 外键约束的作用：

   • 外键约束用于建立表之间的关系，确保引用的数据在被引用表中存在。
   • 它可以配置为级联更新或级联删除，以维护数据的一致性。
   • 外键约束有助于防止孤立数据和维护数据的完整性。

5. 检查约束的作用：

   • 检查约束定义了列中允许的值的范围或条件。
   • 它用于确保数据满足特定规则，例如日期范围、数值范围等。

6. 约束的创建和管理：

   • 约束可以在表的创建时定义，也可以在后续使用ALTER TABLE语句进行修改或删除。
   • 可以为列级别或表级别的约束，具体取决于约束的作用范围。

在MySQL中，正确使用约束可以帮助您设计更加健壮和可靠的数据库，确保数据的完整性和一致性，同时提供了数据关系的强大功能。深入了解和熟练应用这些约束对于数据库开发和管理非常重要。