SQL constraint on values does not work

Question

I'm trying to constrict a column to specific values, with no luck.
Here's what I have been trying:

mysql> CREATE TABLE my_table (
    -> name VARCHAR(20),
    -> sex CHAR(1) CHECK (sex IN('F','M'))
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO my_table VALUES
    -> ('John','D');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM my_table;
+------+------+
| name | sex  |
+------+------+
| John | D    |
+------+------+
1 row in set (0.00 sec)

mysql>

as you can see, the constraint was not enforced...

look into this http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working — KrazzyNefarious, Mar 21 '14 at 13:22

Rahul Tripathi · Accepted Answer · 2014-03-21T13:27:10.723

This is because the CHECK clause is parsed but ignored by all storage engines.

You can create a trigger for that to check the value of Sex column before insert begins.

Try like this:

DELIMITER $$
CREATE TRIGGER `mytrigger` BEFORE INSERT ON `Table`
FOR EACH ROW
BEGIN
    IF SEX <> 'M' or SEX <> 'F' THEN
    SIGNAL SQLSTATE '123'
        SET MESSAGE_TEXT := 'check constraint on Table.Sex failed';
    END IF;
END$$   
DELIMITER ;

score 0 · Answer 2 · answered Mar 21 '14 at 13:42

You can do this without a trigger by using a foreign key constraint:

CREATE TABLE sexes (
  sex char(1) not null primary key
);

INSERT INTO sexes(sex)
    select 'F' union all select 'M';

CREATE TABLE my_table (
  name VARCHAR(20),
  sex CHAR(1) not null references sexes(sex)
);

There may be other reasons why you would want a code table as well. For instance, you might want to store the full name of the code.

Another approach in MySQL is to use an enumerated type for the values.

score 0 · Answer 3 · answered Mar 21 '14 at 13:45

Replace your check constraint with a referential constraint. Add a referential constraint to some table that has a row for male and a row for female. You get one bonus with this method. You can at a later date add more genders to handle cases like earth worms.

CREATE TABLE your_table
(
name varchar(20),
gender char(1),
CONSTRAINT fk_sez FOREIGN KEY (gender)
REFERENCES sezs(gender)
)

Here's a google search for foreign key constraints tutorial

SQL constraint on values does not work

3 Answers3