mysql workbench check约束,MySql Workbench 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);

register attribute should not more than classSize attribute and less than 0.

available attribute should not more than classSize attribte and less than 0.

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

Thank you.

解决方案

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

$$

You need to define the same trigger for UPDATEs.