约束-1-约束

发布于：2025-07-11 ⋅ 阅读:(19) ⋅ 点赞:(0)

约束-1-约束

约束

概述

  1. 概念

    约束是作用于表中字段上的规则, 用于限制存储在表中的数据

  2. 目的

    保证数据库中数据的正确、有效性和完整性

  3. 分类

    约束 描述 关键字
    非空约束 限制该字段的数据不能为 null NOT NULL
    唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
    主键约束 主键是一行数据的唯一标识, 要求非空且唯一 PRIMARY KEY
    默认约束 保存数据时, 如果未指定该字段的值, 则采用默认值 DEFAULT
    检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK
    外键约束 用来让两张表的数据之间建立连接, 保证数据的一致性和完整性 FOREIGN KEY

  4. 注意

    约束是作用于表中字段上的, 可以在创建表/修改表的时候添加约束

演示

根据下面需求, 撰写出建表语句并且创建表结构

字段名 字段含义 字段类型 约束条件 约束关键字
id ID唯一标识 int 主键并且自动增长 PRIMARY KEY, AUTO_INCREMENT
name 姓名 varchar(10) 不为空, 并且唯一 NOT NULL, UNIQUE
age 年龄 int 大于 0, 并且小于等于 120 CHECK
status 状态 char(1) 如果没有指定该值, 默认为 1 DEFAULT
gender 性别 char(1) 无 
create table per_info
(
    id     int primary key auto_increment comment 'ID唯一标识',
    name   varchar(10) not null unique comment '姓名',
    age    int check ( age > 0 && age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '人员信息表';

运用现前已经学习过的知识, 我们很人唔易就可以写出上面一样的表构建语句

然后建好表之后我们可以插入数据, 注意这一部分数据不能违反约束, 否则无法插入, 下面给各位准备好了一些模拟的数据

INSERT INTO per_info (name, age, status, gender)
VALUES ('张伟', 28, '1', '男'),
       ('王芳', 32, '1', '女'),
       ('李强', 45, '2', '男'),
       ('刘洋', 19, '1', '男'),
       ('陈晓', 67, '3', '女'),
       ('杨帆', 5, '4', '男'),
       ('赵敏', 31, '1', '女'),
       ('周华', 88, '2', '男'),
       ('吴婷', 24, '1', '女'),
       ('郑浩然', 42, '1', '男'),
       ('孙丽娟', 56, '3', '女'),
       ('马明宇', 10, '4', '男'),
       ('林婉儿', 115, '2', '女'),
       ('黄建国', 73, '3', '男'),
       ('徐静蕾', 39, '1', '女'),
       ('高圆圆', 36, '1', '女'),
       ('朱志文', 62, '2', '男'),
       ('欧阳雪', 29, '1', '女'),
       ('司马光', 8, '4', '男'),
       ('东方明', 101, '2', '男');

然后, 我们可以来验证一下约束是否可用

  1. 违反唯一约束（重复姓名）

    INSERT INTO per_info (name, age, gender)
VALUES ('张伟', 30, '男');

    违反唯一约束

    数据插入操作违反了唯一约束，具体错误信息为 Duplicate entry '张伟' for key 'per_info.name', 这表明在 per_info 表的 name 字段中已存在名为 张伟 的记录

  2. 违反检查约束（年龄超过120）

    INSERT INTO per_info (name, age, gender)
VALUES ('测试员', 121, '男');

    违反检查约束

    如果我们插入一个超过 CHECK 约束规定的年龄段的年龄数字就会引发错误: Check constraint 'per_info_chk_1' is violated., 这表明我们所插入的数据违反了 per_info_chk_1 约束

  3. 违反非空约束（姓名为空）

    INSERT INTO per_info (name, age, gender)
VALUES (null, 25, '男');

    违反非空约束

    上面语句, 我们将 name 字段设置为 null 了, 由于建表的时候我们使用了非空约束, 所以触发了错误: Column 'name' cannot be null

  4. 违反长度约束（姓名超长）

    INSERT INTO per_info (name, age, gender)
VALUES ('超长姓名测试超过十个字符', 30, '女');

    违反长度约束

    上面语句, 我们传入了一个超过长度限制的字符串作为 name 字段的值, 故而引发了错误: Data truncation: Data too long for column 'name' at row 1

  5. 违反主键约束（重复ID）

    INSERT INTO per_info (id, name, age, gender)
VALUES (1, '主键测试', 40, '男');

    违反主键约束

    上面语句我们输入的数据的 id 已经存在于表 per_info 中, 违反了主键约束, 所以引发了错误: Duplicate entry '1' for key 'per_info.PRIMARY'

  6. 注意事项

    这里其实我们还有一些问题是需要注意的, 下面会给大家稍微列出来一些我已经知道的

    • 当我们往存在有自增字段的表中插入数据的时候, 需要注意, 哪怕数据插入失败了, 自增的字段也已经进行了一次自增, 比如该表的 id 字段, 因为我们执行语句的时候需要先有 id 字段, 然后才能尝试插入, 最后才会因为约束而导致插入失败, 也就是说已经向数据库申请了一个自增 id, 已经使用掉了, 哪怕最后因为数据插入失败, 自增 id 也不会进行回收

外键约束

概念

外键是用来让两张表的数据建立起连接, 从而保证数据的一致性和完整性, 简单来说, 外键也是一个创建的范围规则, 如果需要插入某条数据, 其中有一个字段存在有外键约束的话, 那么就只能插入约束范围之内的数据

班级id外键

如上图所示, 红色框括起来的是班级的 id, 左边的是学生表, 右边的是班级的表, 因为只是一个示例, 所以并没有那么完整

可以发现, 外键分为两部分, 一部分在表(我先称之为: 主表)中一定是主键字段, 另一个部分在表(先称为: 副表)中的值必须是主表中已经存在的值, 否则就会引发外键的异常

再稍微做一个提醒, 因为创建外键是需要存在两个表的嘛, 那么就需要有一个创建表的先后顺序, 需要先将主表创建好, 然后再创建副表, 才能在副表中设置外键字段, 可能大家会说这不是很基本的吗? 我也知道这个很基本, 但我就是有犯过这样子的问题, 而且当时和老师找了 10min 都没找出建表语句有什么问题, 就很有趣

其实外键这个东西, 不一定能用的上, 有些企业可能是会有禁用外键约束的规定, 那这个就跟企业需求有关联了, 不过还是得学嘛

语法

  1. 添加外键

    • 创建表的时候

      CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

    • 表已经存在的时候

      ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

  2. 删除外键

    当你想要删除主表的主键的时候, 如果副表中外键字段还存在有和主表主键等值的数据的话, 就会引发外键异常, 因为副表中还有关联着对应主表的数据, 只有将副表中的相关数据剔除完才可以更新对应主表

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

案例演示

  1. 创建外键约束

    下面我们会以部门表和员工表的副键关联创建作为案例进行演示

    -- 主表：部门表
CREATE TABLE departments
(
    dept_id   INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID',
    dept_name VARCHAR(20) NOT NULL UNIQUE COMMENT '部门名称',
    location  VARCHAR(20) DEFAULT '总部' COMMENT '办公地点'
) COMMENT ='部门信息主表';

-- 从表：员工表
CREATE TABLE employees
(
    emp_id   INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    emp_name VARCHAR(20) NOT NULL COMMENT '员工姓名',
    dept_id  INT         NOT NULL COMMENT '所属部门ID',
    salary   DECIMAL(10, 2) CHECK (salary > 0) COMMENT '薪资',

    -- 外键约束（联级更新/删除）
    CONSTRAINT fk_dept
        FOREIGN KEY (dept_id)
            REFERENCES departments (dept_id)
            ON UPDATE CASCADE
            ON DELETE RESTRICT
) COMMENT ='员工信息从表';

    创建好后, 表结构应该如下图一般, 这样就可以保证主副表的 公寓id 的一致性和完整性

    创建副键

    然后我们可以进行数据插入

    -- 插入部门数据（主表）
INSERT INTO departments (dept_name, location)
VALUES ('技术部', '北京'),
    ('销售部', '上海'),
    ('财务部', '深圳'),
    ('人力资源', '广州');

-- 插入员工数据（从表）
INSERT INTO employees (emp_name, dept_id, salary)
VALUES ('张三', 1, 15000.00), -- 技术部
    ('李四', 2, 12000.00), -- 销售部
    ('王五', 3, 18000.00), -- 财务部
    ('赵六', 1, 22000.00), -- 技术部
    ('钱七', 2, 9500.00); -- 销售部

    先演示一个错误案例, 当我主表没有数据的时候直接插入副表的数据

    外键错误案例

    这个错误就很经典了, 没有办法添加或者修改表中的数据, 因为触发了外键异常 Cannot add or update a child row: a foreign key constraint fails (test.employees, CONSTRAINTfk_deptFOREIGN KEY (dept_id)

    下面就是正确的案例, 先插入主表的数据, 再插入副表的数据

    外键建表正确示例

  2. 删除外键

    alter table employees drop foreign key fk_dept;

    删除外键

    删除外键约束之后就可以插入主表中不存在的数据, 当然这个时候也不能叫主表了, 因为主从关系已经随着外键的去除也被断开了

外键删除更新行为

  1. 删除/更新行为

    行为 说明
    NO ACTION 不采取实施策略: 当在父表中删除/更新对应记录时, 首先检查该记录是否有对应外键, 如果有则不允许更新/删除. (与 RESTRICT 一致)
    RESTRICT 限制策略:当在父表中删除/更新对应记录时, 首先检查该记录是否有对应外键, 如果有则不允许更新/删除. (与 NO ACTION 一致)
    CASCADE 级联策略: 当在父表中删除/更新对应记录时, 首先检查该记录是否有对应外键, 如果有, 则也删除/更新外键在字表中的记录
    SET NULL 置空策略当在父表中删除对应记录时, 首先检查该记录是否有对应外键, 如果有则设置子表中该外键的值为null, (这就要求该外键允许取null)
    SET DEFAULT 父表有变更时, 子表将外键列设置成一个默认的值(Innodb不支持)

  2. 语法

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) [ON UPDATE reference_option] [ON DELETE reference_option]

    • ON UPDATE/ON DELETE: 指定参照动作相关的 SQL 语句. 可为每个外键指定对应于 DELETE 语句和 UPDATE 语句的参照动作.

    • reference_option: 指定参照外键约束的实现策略. 其中, 当没有明确指定外键约束的实现策略时, 两个参照动作会默认使用 RESTRICT. 具体策略可选值如删除/更新行为表所示

外键删除更新行为案例演示

下面有两张表, 分别为部门信息表 departments 和员工信息表 employees, 可以看见他们虽然有同样的字段 dept_id 部门id, 但是从图上看并没有联系, 因为没有外键

无外键关系的

为部门信息表 departments 和员工信息表 employees 添加主从关系的语句如下

alter table employees
    add constraint fk_emp_dept_id foreign key (dept_id) references departments (dept_id) on update cascade on delete cascade;

执行上面语句后, 就可以将两张表联系起来, 从而得到一个从属关系, 从下图可以看出来 employees 表指向了 departments 表, 这就是添加了外键约束后的样子

外键关系图

建立了级联关系的外键约束后, 我们可以进行尝试删除 departments 中的某个 dept_id 信息, 然后查看 employees 表验证更改是否成立

先使用 SELECT 查看员工信息表的样子方便后面进行对比

select *
from employees;

员工信息表被删除前

再查看部门信息表删除数据前的样子

部门信息表删除前

我们尝试删除 dept_id 为 1 的部门, 然后观察 departments 表和 employees

delete departments
from departments
where dept_id = 1;

查看部门信息表

select *
from departments;

部门信息表删除后

查看员工信息表

select *
from employees;

员工信息表删除后

观察可得, 在部门信息表的信息被删除后, 员工信息表的信息也会被同步删除, 这就是级联策略的作用, 不论是更新还是删除, 对应的数据都会同步进行更新或者删除.

外键约束注意

  1. 被参照表(主表) 必须已存在或是当前正在创建的表. 当参照表与被参照表为同一表时, 称为自参照表, 这种结构实现了自参照完整性.

  2. 被参照表必须已定义主键.

  3. 参照时必须明确指定被参照表的列名(或列组合), 且该列(组合)必须是其主键或候选键.

  4. 外键包含的列数必须与被参照表的对应键列数完全相同.

  5. 外键各列的数据类型必须与被参照表主键(或候选键) 对应列的数据类型严格匹配.

  6. 虽然主键禁止空值, 但外键允许包含空值. 外键数据只要满足: 每个非空值均存在于被参照表的主键中, 即视为有效.

小结

  1. 非空约束

    在 MySQL 中, 非空约束可以通过在 CREATE TABLE 或 ALTER TABLE 语句中, 某个列定义后面加上关键字 NOT NULL来定义, 用来约束该列的取之不能为空.

  2. 唯一约束

    唯一约束作用于类似身份证等不允许出现重复的地方, 此时需要使用 UNIQUE 关键字进行约束.

  3. 主键约束

    主键可以是表中的某一列, 也可以是表中多个列构成的一个组合; 其中, 由多个列组合而成的主键也称为复合主键. 在 MySQL 中, 主键比须遵守以下规则.

  4. 默认约束

    如果在插入数据的时候设置了默认约束的字段没有插入值, 则使用默认值.

  5. 检查约束

    与非空约束一样, 检查约束也可以通过在 CREATE TABLE 或 ALTER TABLE 语句中, 根据用户的实际完整性要求来定义. 它可以分别对列或表实施 CHECK 约束

  6. 外键约束

    一旦涉及到外键约束, 那么至少需要数据库中存在两张表, 外键约束是用来保证数据的一致性和完整性的工具.

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布