MySQL 触发器可以模拟 CHECK 约束吗?

2021-11-20 00:00:00 constraints mysql triggers

我想在 MySQL 中使用 CHECK 约束，但它不受支持.(与其他 RDBMS 不同，它会理解但不强制执行 CHECK.)

I want to use the CHECK constraint in MySQL, but it is not supported. (Unlike other RDBMS, it will understand but not enforce the CHECKs.)

我已经看到了一些使用触发器的解决方法.但他们倾向于为相关字段设置默认值，而不是返回错误.

I have seen some workarounds with triggers. But they tend to set a default value to the field in question instead of returning an error.

是否可以构造一个触发器，在不满足条件时返回错误?

Is it possible to construct a trigger that returns an error if a condition is not met?

最终我想要一个复制 CHECK 约束的触发器.

Ultimately I want a trigger that copies a CHECK constraint.

推荐答案

试试下面的语法

CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

相关文章