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);
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 UPDATE
s.