Mysql Create Trigger,Check constraint in mysql

CHECK constraints are not supported by MySQL. So use trigger, Here check zero or empty value of the code column.

BEFORE INSERT Trigger means that MySQL will fire this trigger before the INSERT operation is executed.

Syntax

CREATE TRIGGER trigger_name
BEFORE INSERT
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Create Table.

create table test(name varchar(100),Code varchar(100))

Trigger Examples:

CREATE TRIGGER test1 BEFORE INSERT  ON test 
FOR EACH ROW 
BEGIN 
IF NEW.Code='0' then 
   SIGNAL SQLSTATE '12345' 
     SET MESSAGE_TEXT = 'check constraint 
     on Test.Code 0 is not allowed';

ELSE IF NEW.Code='' THEN
   SIGNAL SQLSTATE '12345' 
     SET MESSAGE_TEXT = 'check constraint 
    on Test.Code '' '' is not allowed';
End IF;
END IF; 
END

Insert Records.

INSERT INTO test (name,Code) values('t','0')

Result: