MySql Workbench CHECK约束 [英] MySql workbench CHECK constraint

问题描述

在这里，我想在将记录插入数据库之前创建2个CHECK约束.

Here I want to create 2 CHECK constraint before the record insert to the database.

ALTER TABLE SubjectEnrollment
ADD CONSTRAINT register CHECK (register <= classSize AND register >=0),
ADD CONSTRAINT available CHECK (available <= classSize AND available >= 0);

1. register属性不应大于classSize属性且小于0.
2. 可用属性不应大于classSize属性且小于0.

当我在MySql Workbench中键入此语法时，它抱怨语法错误:意外的'CHECK'(检查)'.我应该如何使用TRIGGER添加这些?

When I type in this syntax in MySql Workbench, it complaints "Syntax Error: unexpected 'CHECK' (check)'. How should I add these, using TRIGGER?

谢谢.

推荐答案

由于MySQL不支持check，因此需要一个触发器.像这样的CREATE触发器:

Since MySQL does not support check, you need a trigger for that. Something like this CREATE trigger:

delimiter $$
CREATE TRIGGER some_trigger_name
BEFORE INSERT ON SubjectEnrollment
FOR EACH ROW
BEGIN    
    IF (NEW.register > NEW.classSize OR NEW.register < 0)        
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid data';
    END IF;
END
$$

您需要为UPDATE定义相同的触发器.

You need to define the same trigger for UPDATEs.

这篇关于MySql Workbench CHECK约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！