MySQL 入门教程
  • 教程目录
  • 第一章 基础教程
    • 第一节 入门
      • 1.1 安装
      • 1.2 示例数据库
    • 第二节 查询数据
      • 2.1 SELECT 语句
      • 2.2 SELECT DISTINCT 语句
    • 第三节 过滤数据
      • 3.1 WHERE 子句
      • 3.2 AND 运算符
      • 3.3 OR 运算符
      • 3.4 IN 运算符
      • 3.5 BETWEEN 运算符
      • 3.6 LIKE 运算符
      • 3.7 LIMIT 子句
      • 3.8 IS NULL 运算符
    • 第四节 数据排序
      • 4.1 ORDER BY 子句
      • 4.1 自然排序
    • 第五节 连接表
      • 5.1 别名
      • 5.2 INNER JOIN
      • 5.3 LEFT JOIN
      • 5.4 自连接
      • 5.5 CROSS JOIN
    • 第六节 分组数据
      • 6.1 GROUP BY子句
      • 6.2 HAVING 子句
    • 第七节 子查询,派生表和通用表达式
      • 7.1 子查询
      • 7.2 派生表
      • 7.3 公共表表达式
      • 7.4 递归 CTE
    • 第八节 集合操作符
      • 8.1 UNION 和 UNION ALL
      • 8.2 INTERSECT 模拟
    • 第九节 修改数据
      • 9.1 INSERT 语句
      • 9.2 INSERT IGNORE
      • 9.3 UPDATE 语句
      • 9.4 UPDATE JOIN 语句
      • 9.5 DELETE
      • 9.6 ON DELETE CASCADE.
      • 9.7 DELETE JOIN
      • 9.8 REPLACE 语句
      • 9.9 PREPARE 语句
    • 第十节 事务
      • 10.1 事务介绍
      • 10.2 表锁定
    • 第十一节 管理数据库和表
      • 11.1 数据库管理
      • 11.2 MySQL 表类型
      • 11.3 CREATE TABLE
      • 11.4 序列
      • 11.5 ALTER TABLE
      • 11.6 重命名表
      • 11.7 从表中删除列
      • 11.8 向表中添加新列
      • 11.9 删除表
      • 11.10 临时表
      • 11.11 TRUNCATE TABLE
    • 第十二节 索引
      • 12.1 管理索引
      • 12.1 UNIQUE 索引
    • 第十三节 数据类型
      • 13.1 INT
      • 13.2 DECIMAL
      • 13.3 BIT
      • 13.4 BOOLEAN
      • 13.5 CHAR
      • 13.6 VARCHAR
      • 13.7 TEXT
      • 13.8 DATE
      • 13.9 TIME
      • 13.10 DATETIME
      • 13.11 TIMESTAMP
      • 13.12 JSON
      • 13.13 ENUM
    • 第十四节 约束
      • 14.1 NOT NULL 约束
      • 14.2 主键约束
      • 14.2 外键约束
      • 14.4 UNIQUE 约束
      • 14.5 CHECK 约束
    • 第十五节 全球化
      • 15.1 字符集
      • 15.2 校对规则
    • 第十六节 导入和导出
      • 16.1 导入 CSV 文件
      • 16.2 导出为 CSV
  • 第二章 技巧
    • CTE 简介
    • 递归 CTE
    • 邻接列表模型和层次结构
    • 获取行数
    • 比较表
    • 找重复值
    • 删除重复行
    • UUID 和主键
    • 复制表数据
    • 变量
    • 生成列
    • 连续行比较
    • 更改存储引擎
    • 基于正则表达式的搜索
    • row_number 模拟
    • 随机选择记录
    • 选择第 n 个最高纪录
    • 重置自动增量值
    • MariaDB 与 MySQL 比较
    • 间隔
    • NULL 详细和应用
    • 获取今天的日期
    • 将NULL值映射到有意义的值
    • 注释
  • 第三章 存储过程
    • 简介
    • 入门
    • 变量
    • 参数
    • 返回多个值
    • IF语句
    • CASE语句
    • IF和CASE语句的技巧
    • 循环
    • 游标
    • 列出存储过程
    • 错误处理
    • SIGNAL 和 ESIGNAL 语句
    • 存储函数
  • 第四章 触发器
    • 实现
    • 创建
    • 创建多个触发器
    • 管理
    • 计划事件
    • 修改事件
  • 第五章 视图
    • 简介
    • 实现和限制
    • 创建
    • 可更新视图
    • 确保视图一致性
    • 检查选项子句
    • 管理
  • 第六章 全文搜索
    • 简介
    • 定义索引
    • 自然语言全文搜索
    • 布尔全文搜索
    • 查询扩展
    • ngram全文解析器
  • 第七章 函数
  • 第八章 管理
    • 访问控制系统入门
    • 创建用户
    • 授予权限
    • 撤销权限
    • 角色管理
    • 删除用户
    • 维护数据库表
    • 备份数据库
    • 列出数据库
    • 列出表
    • 列出表的列
    • 列出用户
Powered by GitBook
On this page
  • SQL CHECK约束简介
  • MySQL使用触发器CHECK约束
  • MySQL CHECK约束使用可更新视图与check选项

Was this helpful?

  1. 第一章 基础教程
  2. 第十四节 约束

14.5 CHECK 约束

Previous14.4 UNIQUE 约束Next第十五节 全球化

Last updated 5 years ago

Was this helpful?

在本教程中,您将学习如何使用带有check选项的触发器或视图来模拟MySQL CHECK约束。

注意: 要更好学习和理解本教程,您需要对,视图和有很好的了解。

SQL CHECK约束简介

标准SQL提供的检查(CHECK)约束指定某列中的值必须满足布尔表达式。 例如,您可以添加一个CHECK约束来强制成本(cost)列为正值,如下所示:

USE testdb;
CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL CHECK(cost > 0),
    price DECIMAL (10,2) NOT NULL
);

SQL允许您将一个或多个CHECK约束应用于一列或跨多个列。 例如,为了确保价格(price)列总是大于或等于成本(cost)列,可使用CHECK约束如下:

USE testdb;
CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL CHECK (cost > 0),
    price DECIMAL(10 , 2 ) NOT NULL CHECK (price > 0),
    CHECK (price >= cost)
);

当CHECK约束设置完成,每当插入或更新导致布尔表达式的值计算为false时,则视为违反检查约束,并且数据库系统拒绝插入或更改数据。

MySQL使用触发器CHECK约束

首先,为了演示目的,我们先创建一个parts表,如下语句 -

USE testdb;
CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);
DELIMITER $$

CREATE PROCEDURE `check_parts`(IN cost DECIMAL(10,2), IN price DECIMAL(10,2))
BEGIN
    IF cost < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'check constraint on parts.cost failed';
    END IF;

    IF price < 0 THEN
 SIGNAL SQLSTATE '45001'
 SET MESSAGE_TEXT = 'check constraint on parts.price failed';
    END IF;

    IF price < cost THEN
 SIGNAL SQLSTATE '45002'
            SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
    END IF;

END$$

DELIMITER ;

第三,创建BEFORE INSERT和BEFORE UPDATE触发器。 在触发器中,调用check_parts()存储过程。

-- before insert
DELIMITER $$
CREATE TRIGGER `parts_before_insert` BEFORE INSERT ON `parts`
FOR EACH ROW
BEGIN
    CALL check_parts(new.cost,new.price);
END$$   
DELIMITER ;

-- before update
DELIMITER $$
CREATE TRIGGER `parts_before_update` BEFORE UPDATE ON `parts`
FOR EACH ROW
BEGIN
    CALL check_parts(new.cost,new.price);
END$$   
DELIMITER ;

第四,插入满足以下所有条件的新行:

  • cost > 0

  • price > 0

  • price >= cost

执行以下插入语句 -

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

INSERT语句调用BEFORE INSERT触发器并接受值。

演示-1 以下INSERT语句执行将会失败,因为它违反了条件:cost> 0。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

执行上面插入语句,得到以下错误提示信息 -

Error Code: 1644. check constraint on parts.cost failed

演示-2 以下INSERT语句执行将会失败,因为它违反了条件:price> 0。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);

执行上面插入语句,得到以下错误提示信息 -

Error Code: 1644. check constraint on parts.price failed

演示-3 以下INSERT语句执行将会失败,因为它违反了条件:price > cost。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);

执行上面插入语句,得到以下错误提示信息 -

1644 - check constraint on parts.price & parts.cost failed

现在,让我们来看看在parts表中的数据。

SELECT * FROM parts;

执行上面查询语句,得到以下结果 -

+---------+-------------+------+-------+
| part_no | description | cost | price |
+---------+-------------+------+-------+
| A-001   | Cooler      | 100  | 120   |
+---------+-------------+------+-------+
1 row in set

我们试图更新cost列的值,使其低于价格(price)列:

UPDATE parts
SET price = 10
WHERE part_no = 'A-001';

执行上面更新语句,得到以下错误提示信息 -

Error Code: 1644. check constraint on parts.price & parts.cost failed

上面更新语句被拒绝执行了。

如上示例中所示,我们通过使用两个触发器:BEFORE INSERT和BEFORE UPDATE,来模拟MySQL中的CHECK约束。

MySQL CHECK约束使用可更新视图与check选项

首先,删除parts表以删除所有相关的触发器,并创建一个新的表,与parts表具有相同的结构,但使用了不同的名称:parts_data:

DROP TABLE IF EXISTS parts;

CREATE TABLE IF NOT EXISTS parts_data (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

其次,根据parts_data表创建名为parts的视图。 通过这样做,我们可以保持使用parts表的应用程序的代码保持不变。 此外,旧零件表的所有权限保持不变。

CREATE VIEW vparts AS
    SELECT 
        part_no, description, cost, price
    FROM
        parts_data
    WHERE
        cost > 0 AND price > 0 AND price >= cost 
WITH CHECK OPTION;

第三,通过parts视图向parts_data表中插入一个新行:

INSERT INTO vparts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

上面的新行可被接受,因为新行有效,可以出现在视图中。

但是,以下语句失败,因为新行不会出现在视图中。

INSERT INTO vparts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

执行上面语句,MySQL会发出以下错误 -

Error Code: 1369. CHECK OPTION failed 'testdb.parts_checked'

在本教程中,我们向您介绍了标准SQL CHECK约束和两种在MySQL中模拟CHECK约束的方法用法。

不幸的是,MySQL不支持CHECK约束。 实际上,MySQL在语句中接受CHECK子句,但是它会以静默方式忽略它。

在MySQL中模拟CHECK约束的第一种方法是使用两个:BEFORE INSERT和BEFORE UPDATE。

其次,创建一个来检查cost和price列中的值。

这个方法是使用基于表的check选项来创建一个视图。 在视图的语句中,我们仅选择满足CHECK条件的有效行。对视图的任何插入或更新都将被拒绝,这样使新的行记录不会出现在视图中。

触发器
存储过程
CREATE TABLE
触发器
存储过程
SELECT