No CHECK Constraint in MYSQL
It looks like MYSQL doesn’t really validate for check constraint. I checked for mysql manual and noticed its true.
Copied the following section from MySQL manual. (http://dev.mysql.com/doc/refman/5.5/en/alter-table.html)
The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, “MySQL Differences from Standard
How I came to know this bad behavior of mysql is, I just created a simple table such as below and After creating I assumed the column gender_code would not accept any values other than ‘m’ or ‘f’. but unfortunately it did which pushed me in a dilemma. The bad scene is it didn’t even throw me an error.
CREATE TABLE USER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR( 128 ) NOT NULL,
gender_code CHAR(1) NOT NULL,
CONSTRAINT chk_gender CHECK (gender_code = 'm' OR gender_code = 'f')
);
Hence, in order to have a check constraint in mysql database I found three alternative ways.
Solution 1
Define the data type of column gender_code as ENUM.
‘gender_code’ ENUM(‘m’, ‘f’) NOT NULL
Only disadvantage i noticed in this way, when inserting any other values (not ‘m’or ‘f’) it accepts an empty string.
Solution 2
Create a trigger to check for gender_code ‘m’ or ‘f’ else throw an error.
DROP TRIGGER IF EXISTS chk_gender;
delimiter $$
CREATE TRIGGER chk_gender BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
DECLARE msg varchar(255);
IF (NEW.gender <> 'm' AND NEW.gender<>'f')
THEN
SET msg = concat('Constraint chk_gender violated: gender must not contain invalid value ', cast(new.gender as char));
SIGNAL sqlstate '45000' SET message_text = msg;
END IF;
END$$
delimiter ;
feel this is the best way to go.
Solution 3
To create a lookup table to contain gender code (‘m’ & ‘f’) and define current table’s gender_code as foreign key to refer lookup table.
CREATE TABLE gender_codes
( code CHAR(1) NOT NULL
, PRIMARY KEY (code)
) ENGINE = InnoDB ;
INSERT INTO gender_codes (code) VALUES (‘m’), (‘f’) ;
And then, original table should look like.
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR( 128 ) NOT NULL,
gender_code CHAR(1) NOT NULL,
CONSTRAINT fk_gender FOREIGN KEY (gender_code) REFERENCES gender_codes (gender_code)
);
Hope these workarounds saves someone’s time.