Prevent Insertion if Check Fails

Question

I want to prevent insertion in my MySQL database when the Check condition is not true. For example, in the following MySQL query I have added a check constraint that Gender can only be "M" or "F" but still, I am able to insert "T".

CREATE TABLE STUDENT
(S_FIRST VARCHAR(10) NOT NULL, 
S_LAST VARCHAR(10) NOT NULL, 
GENDER VARCHAR(1) NOT NULL CHECK (GENDER="M" OR GENDER="F"), 
S_EMAIL VARCHAR(50) NOT NULL CHECK (S_EMAIL="%@%.COM"), 
S_PHONE VARCHAR(10) NOT NULL CHECK (S_PHONE<9999999999), 
DATE_OF_BIRTH DATE NOT NULL, 
S_ID INT(3) PRIMARY KEY AUTO_INCREMENT);


INSERT INTO STUDENT VALUE("ABNISH","NIRAJ","T","ABNISHNIRAJ10@GMAIL.COM","7062123799",'1996-10-19',NULL); 

-- the Second query should not work since the Gender is "T"

Possible duplicate of [CHECK constraint in MySQL is not working](https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) — Ravi, Mar 24 '19 at 10:15

score 0 · Answer 1 · answered Mar 24 '19 at 10:11

Check constraints are introduced in MySql Version 8.

Alternative approach is to use triggers

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
    IF NEW.GENDER NOT IN ('F', 'M') THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on GENDER failed';
    END IF;
END$$   
DELIMITER ;

Prevent Insertion if Check Fails

1 Answers1