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);

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.

Before update is making to the table, it should check the class size available before allowing the student to register the subject. If the class only available for 40 students, the 41st student is not allowed to insert to the table any more. — helloworld1234, Aug 20 '16 at 15:12

juergen d · Accepted Answer · 2022-12-09T18:12:15.630

3

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.

edited Dec 09 '22 at 18:12

answered Aug 20 '16 at 15:15

juergen d

201,996
37
293
362

Thanks, may I ask the 'NEW' word is table name? – helloworld1234 Aug 20 '16 at 15:35
`NEW` references the record that is to be inserted in the table. `NEW.register` is the value of `register` you want to insert. BTW if you define an `UPDATE` trigger then also use the word `NEW` for referencing the record you want to update – juergen d Aug 20 '16 at 15:36
Thanks a lot , man... – helloworld1234 Aug 21 '16 at 04:03

score 0 · Answer 2 · answered Aug 20 '16 at 15:12

A bit long for a comment.

MySQL supports the syntax for check, but in the create table:

create table (
    . . . 
    CHECK (register <= classSize AND register >= 0)
);

Check is not actually implemented in any engines, so this does nothing. Presumably for that reason, it is not part of the constraint syntax and hence not available for add constraint.

MySql workbench CHECK constraint

2 Answers2

Linked