MySQL8-约束

最新推荐文章于 2024-08-06 15:46:06 发布

Ren大白

最新推荐文章于 2024-08-06 15:46:06 发布

阅读量1.2k

点赞数 2

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

本文链接：https://blog.csdn.net/rbx508780/article/details/127175805

版权

mysql 专栏收录该内容

6 篇文章 2 订阅

订阅专栏

一、约束的概念理解

约束（CONSTRAINTS）是强制加在表上的一些规定。约束的作用是为了保证数据的完整性，数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。也就是说约束是为了防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

🚀数据的完整性要从以下四个方面考虑：

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

2.根据约束的作用，约束可分为：

PRIMARY KEY 主键(非空且唯一)约束

UNIQUE 唯一键约束，规定某个字段在整个表中是唯一的

FOREIGN KEY 外键约束

NOT NULL 非空约束，规定某个字段不能为空

CHECK 检查约束

DEFAULT 默认值约束

AUTO_INCREMENT自增是键约束字段的一个额外的属性。

3.根据约束的影响范围可分为：

表级约束

所有键约束和检查约束是表级约束，即不仅要看约束字段当前单元格的数据，还要看其他单元格的数据。

列级约束

非空约束和默认值约束都是列级约束，即约束字段只看当前单元格的数据即可，和其他单元格无关。
所有的表级约束都可以在information_schema.table_constraints表中查看。
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

4.约束与索引（INDEX）

在MySQL中键约束会自动创建索引，提高查询效率。索引的详细讲解在后面部分会详细说。

约束是一个逻辑概念，它不会单独占用物理空间，

索引是一个物理概念，它是会占用物理空间。

例如：字典

字典里面有要求，不能有重复的字（字一样，读音也一样），这是约束。

字典里面有“目录”，它可以快速的查找某个字，目录需要占用单独的页。

二、非空约束

作用：非空约束用于保证数据表中某个字段的值不为NULL

关键字：NOT NULL

特点：非空约束只能出现在表对象的列上限定当前列值非空

默认所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型

空字符串''不等于NULL，0也不等于NULL

添加与删除非空约束示例：

CREATE TABLE student(
  Id int,
  Name varchar(50) NOT NULL,	-- 创建表示添加非空约束--
  tel char(11)
);
INSERT INTO student values(1,’tom’,null);

-- 为已存在表的列添加非空约束(了解)--
ALTER TABLE student MODIFY tel char(11) NOT NULL;

-- 删除非空约束（了解）--
ALTER TABLE student MODIFY tel char(11);

三、唯一键约束

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

关键字：UNIQUE

特点：数据唯一，可以为NULL，每个表可以有多个唯一键约束。

可以多个列组合的值唯一。

在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

MySQL会给唯一约束的列上默认创建一个唯一索引。

删除唯一约束只能通过删除唯一索引的方式删除。

添加与删除唯一约束示例：

创建表时添加唯一约束

#方式一：
CREATE TABLE student(
  Id int PRIMARY KEY,
  Name varchar(50) UNIQUE,
  tel char(11) UNIQUE
);

#方式二：方便创建联合唯一键
CREATE TABLE student(
  Id int PRIMARY KEY,
  Name varchar(50),
  tel char(11),
  UNIQUE KEY(Name),
  UNIQUE KEY(tel)
);

创建表后添加唯一约束

CREATE TABLE student(
  id int,
  name varchar(50)
);
-- 为已存在表添加唯一约束--
ALTER TABLE student ADD UNIQUE (name);

删除唯一键约束（索引）

ALTER TABLE student DROP INDEX name;

查看表的索引

show index from 表名称;

四、主键约束

作用：用于唯一地标识表中的某一条记录。

关键字：PRIMARY KEY

特点：数据唯一，且不能为NULL（相当于唯一约束与非空约束的组合），每个表中最多有一个主键。

当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

为表添加主键约束操作示例：

方式一：创建表时添加主键约束
CREATE TABLE student(
  id int PRIMARY KEY,
  name varchar(50)
);

方式二：此种方式优势在于，可以创建联合主键
CREATE TABLE student(
  id int,
  name varchar(50),
  PRIMARY KEY(id)
);

创建联合主键
CREATE TABLE student(
  classid int,
  stuid int,
  name varchar(50),
  PRIMARY KEY(classid，stuid)
);

创建表后添加主键约束
CREATE TABLE student(
  id int,
  name varchar(50)
);
-- 为已存在的表添加主键约束--
ALTER TABLE student ADD PRIMARY KEY (id);

删除主键约束
alter table student drop primary key;

五、自动增长列（自增）

自增是键约束字段的一个额外的属性。

作用：给主键添加自动增长的数值，默认从初始值1开始自增。当需要产生唯一标识符或顺序值时，可设置自增长

关键字：AUTO_INCREMENT

SQL Server数据库 (identity) ，Oracle数据库(sequence)

特点与要求：

一个表最多只能有一个自增长列

自增长列约束的列必须是键列（主键列，唯一键列等），通常是主键

自增约束的列的数据类型必须是整数类型

如果自增列指定了 0 值和 null值，会在当前最大值的基础上自增；如果自增列手动指定了其它具体值，直接赋值为指定的具体值。

添加与删除自增示例：

添加自增约束
CREATE TABLE student(
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(50)
);

INSERT INTO student(name) values(‘tom’);


CREATE TABLE student(
  id int PRIMARY KEY,
  name varchar(50)
);
-- 为已存在表的列添加自增约束--
ALTER TABLE student MODIFY id INT  AUTO_INCREMENT;

删除自增列

alter table student modify id INT;

六、默认值约束

作用：给某个字段/某列指定默认值

关键字：DEFAULT

特点：一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。常与非空约束一起使用

操作示例：

CREATE TABLE student(
  Id int pirmary key,
  Name varchar(50) NOT NULL,
  gender char NOT NULL DEFAULT '男' -- 创建表时，为gender字段添加默认值 --
);
insert into student values(1,'lily','女');

insert into student values(2,'jerry',DEFAULT);
-- 修改gender默认值为‘女’
ALTER TABLE student MODIFY gender char DEFAULT '女';
-- 删除gender字段默认值约束，如果有非空约束，也一并删除
alter table student modify gender char;

七、CHECK约束

作用：检查某个字段的数据是否符合指定的要求。一般指的是值的范围

关键字：CHECK

说明：从 MySQL 8.0.16 开始支持CHECK约束，CREATE TABLE 允许所有存储引擎的表和列 CHECK 约束的核心功能。

操作示例：

CREATE TABLE t1(
  CHECK (c1 <> c2), -- 表约束：支持引用其他列名
  c1 INT CHECK (c1 > 10), -- 列约束：只能引用当前正在定义的列名
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),-- 列约束
  c3 INT CHECK (c3 < 100),-- 列约束
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),-- 表约束
  CHECK (c1 > c3)-- 表约束
);
-- ----------------------
CREATE TABLE student(
	id int PRIMARY key auto_increment, -- 自增列不能使用check约束
	name varchar(10),
	age int check(age>=18 and age<100),
    gender char check(gender in('男','女'),
    sex char enum('m','f'),
    work_city set('bj','sh','sz','gz')                  
);
insert into student value(1,'tom',19,'男','m','bj');
insert into student value(2,'rose',19,'女','f','bj,sz');

建表后添加check约束

#如何在建表后添加检查约束，使用add check
alter table 表名称 add check(条件);

删除check约束

alter table 表名称 drop check 检查约束名;

八、外键约束

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

引用完整性是对实体之间关系的描述，即外键与引用键之间的引用规则，外键约束的作用就是保证这种关系准确可靠。

关键字：FOREIGN KEY

相关概念：

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
外键：从表中的某个字段，引用自主表中的某个字段或多个字段
引用键：主表中被引用的字段

例如：员工表的员工所在部门这个字段did的值要参考部门表的字段id。

部门表是主表，员工表是从表。

员工表的did字段为外键，对应的引用键为的部门表的id字段

特点：

从表的外键列，必须引用/参考主表的主键或唯一约束的列，通常是主键。为什么？因为被依赖/被参考的值必须是唯一的
外键和引用键中的对应列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
删表时，先删从表（或先删除外键约束），再删除主表
在“从表”中指定外键约束，并且一个表可以建立多个外键约束

操作示例：

第一种添加外键约束的方式：创建从表时添加

-- 主表--
CREATE TABLE student(
  sid int primary key,
  name varchar(10) not null,
  sex char(10) default '男'
);


-- 从表--
create table score(
  id int,
  score int,
  sid int ,	 -- 外键，列的数据类型一定要与主键的类型一致
  CONSTRAINT fk_stu_score foreign key(sid) references student(sid)
);

第二种添加外键方式：创建从表时未添加外键约束，通过修改从表添加外键约束。

ALTER table score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(sid);

删除外键约束

ALTER TABLE score DROP FOREIGN KEY fk_stu_score;

Ren大白

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录