MySQL约束

最新推荐文章于 2024-04-25 12:02:52 发布

saneryi447

最新推荐文章于 2024-04-25 12:02:52 发布

阅读量690

点赞数

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

本文链接：https://blog.csdn.net/qq_69047657/article/details/128170134

版权

MySQL 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

为什么需要约束

数据完整性是指数据的精确性和可靠性，他是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的

为了保证数据的完整性，SQL规范以约束的方式对数据进行额外的条件限制，从以下四个方面考虑：

实体约束（Entity Integrity）：例如：同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要找到这个部门
用户自定义完整性：（User-dafind Integrity）：例如用户名唯一，密码不能为空，本部门经理的工资不能高于本部门职工工资的平均工资的五倍

什么是约束

约束是表级的强制规定

可以在创建镖时规定约束（通过CREATE TABLE语句），或者在表创建后通过ALTER TABLE 语句进行约束

约束作用范围

列级约束：将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明完以后

约束作用

1,not null (非空约束)
2，unique （唯一约束）
3，primary key（主键约束）
4，foreign key （外键约束）
5，check（检查约束）
6，default（默认值约束）

如何添加约束

CREATE TABLE 时添加约束

ALTER TABLE 时增加约束，删除约束

查看表中约束

#information_schema 数据库名（系统库）
#table_constraints（专门存储各个表中的约束）
SELECT * FROM information_schema.table_constraints WHERE table_name='表名称';

非空约束

作用

限定某个字段/系列的值不允许为空

关键字

NOT NULL

特点

默认，所有的类型的值都可以是NULL ，包括int float 等数据类型
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
一个表可以有很多列都分别限定了非空
空字符“不等于null，0也等于null”

#在ALTER TABLE 时添加约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
#在ALTER TABLE 时删除约束
ALTER TABLE 表名 MODIFY 列名 数据类型  NULL;

唯一约束

作用

用来限制某个字段/某列的值不能重复

#在CREATE TABLE时添加约束
CREATE TABLE test2(
id int UNIQUE,
last_name varchar(46),
email varchar(25) UNIQUE,
salary decimal(10,2)
# 表级约束
CONSTRAINT 约束名 UNIQUE（列名）
);

#在ALTER TABLE 时添加约束
ALTER TABLE 表名 ADD CONSTRAINT UNIQUE (列名)
#复合的唯一性约束
CREATE TABLE "表名"(
id int,
password varchar(45),
CONSTRAINT "约束名" UNIQUE (id,password)
)
#删除唯一性约束
	添加唯一性约束的列上也会自动创建唯一索引
	删除唯一约束只能通过删除唯一索引的方式删除
	删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样
	如果创建唯一索引时未指定名称，如果是单列，就默认与列名相同，如果是组合约束，则按组合第一个列名，默认命名

#注意：可以通过 show index from 表名称;  查看表索引

PRIMARY KEY 约束

作用

用来唯一标识表中的一行记录

关键字

primary key

特点

主键约束相当于为唯一约束+非空约束的组合，主键约束不允许重复出现，也不允许出现空值
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别创建
主键对应着表中的一列，或者多列
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
MySQL的主键名总数primary，就算自己命名主键约束名也没用
当创建主键约束时，系统默认会在所在的列或组合上建立对应的主键索引，如果删除主键约束了，主键约束也对应的索引就自动删除
需要注意的一点是，不要修改主键的字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性

#如何删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

自增长：AUTO_INCREMENT

作用

某个字段的值自增

关键字

auto_increment

特点和要求

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可以设置自增长
自增长列约束的列必须是主键列，或唯一列
自增约束的列的数据类型必须是整型
如果自增长指定了0或null，会在当前最大值的基础上自增，如果自增列手动指定了具体值，直接赋值为具体值

FOREIGN KEY 约束·

作用

限定某个表的某个字段的引用完整性

特点

从表的外键列，必须引用/参考主表的主键或唯一约束的列
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名
创建表时就指定外键约束的话，先创建主表，再创建从表
删除表，先删除从表，再删除主表
当主表记录被从表删除时，主表记录将不允许删除，如果要删除数据，需要先删除从表中依赖记录的数据，然后才可以删除主表的数据
在从表总会指点外键约束，并且一个表可以创建多个外键约束
从表的外键列与主键被参照的列名可以不相同，但数据类型必须一样，逻辑意义一致，如果类型不一样，创建子表时，就会出现错误
当创建外键约束时，系统默认会在所在列上建立对应的普通索引。但是索引名不是列名，不是外键的约束名
删除外键约束后，必须手动删除对应的索引

#创建外键约束
CREATE TABLE dept1(
dept INT,
dept_name VARCHAR(15)
)
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept)
);
# 添加外键约束
alter table 表名 add CONSTRAINT 约束名 FOREIGN KEY(从表列名) REFERENCES 主表(主表列名)

约束等级

Cascade方式：在父表update/delete记录时，同步update/delete掉子表的记录
Set bull方式：在父表update/delete记录时，将子表上匹配记录的列设为nulll，但要注意子表的外键列不能为not null

#对于外键约束，最好采用：on update cascade on delete set null
FOREIGN KEY(department_id) REFERENCES dept1(dept) ON UPDATE CASCADE ON DELETE SET NULL
# 删除外键约束
alter table 从表名 drop foreign key 外键约束名

CHECK约束

作用

检查某个字段的值是否符合xx要求

#check 在5.7不支持
create table 表名（
eid int,
ename varchar(45),
gender char check('男' or'女')

）

DEFAULT 约束

作用

设置数据的默认值