MySQL基础篇-约束-CSDN博客

目录

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

 执行这样的sqlname字段为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中正确使用约束可以帮助您设计更加健壮和可靠的数据库确保数据的完整性和一致性同时提供了数据关系的强大功能。深入了解和熟练应用这些约束对于数据库开发和管理非常重要。