How to use constraints in SQL database?

Question

I am trying to add a constraint on the Gender columns to only accept on M or F.

CREATE TABLE IF NOT EXISTS Coach (
    Address varchar(255),
    Citizenship varchar(50),
    DateOfBirth varchar(15),
    Email varchar(255),
    Emailconfidential tinyint(1),
    Gender varchar(1) check (Gender = "M" or Gender = "F"),
    Name varchar(50),
    Password varchar(100) NOT NULL,
    Phonenumber varchar(30),
    Sport varchar(25),
    Verified tinyint(1),
    Views varchar(100),
    primary key(Email)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

However, when I create a row with a value other than M or F for Gender it accepts it. How do I get the database to prevent accepting inputs other than M or F?

http://dba.stackexchange.com/questions/9662/check-constraint-does-not-work — OldProgrammer, May 07 '16 at 20:46
Possible duplicate of [CHECK constraint in MySQL is not working](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) — Nasreddine, May 07 '16 at 20:46
["For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. **The CHECK clause is parsed but ignored by all storage engines**"](http://dev.mysql.com/doc/refman/5.7/en/create-table.html) — Nasreddine, May 07 '16 at 20:47

score 0 · Answer 1 · answered May 07 '16 at 21:40

In the comnents it has been pointed out that mysql does not enforce the check constraint. However, this particular issue can be solved without a check constraint by using enum data type:

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time

How to use constraints in SQL database?

1 Answers1