MySql workbench CHECK constraint

Question

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 attribute should not more than classSize attribute and less than 0.
2. 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.

Answer 1

Since MySQL prior to version 8.0.16 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.