sql - check constraint won't work mysql -

check constraint won't work

create table if not exists supervisor ( sup_id int(3) not null, sup_name varchar(30) not null, gen varchar(1) not null check (gen='m' or gen='f'), dep_id int(4),  primary key (sup_id), index (dep_id),  foreign key (dep_id)     references department(dep_id)     on update cascade on delete restrict     ); 

i tried:

 constraint chk_supervisor_gen check ('m' or 'f') 

neither of these stopped information being entered

insert supervisor (sup_id, sup_name, gen, dep_id) values          (1, 'hello', 'g', 1); 

mysql doesn't enforce check constraints.

this documented deviation sql standard. (though unexpected uninitiated.)

if need mysql database enforce "check constraint", enforcement has coded before insert , before update trigger.

---

this note:

the check clause parsed ignored storage engines.

is buried in mysql reference manual, under create table syntax.

reference: https://dev.mysql.com/doc/refman/5.5/en/create-table.html

---

warning enum

an enum not restrict "invalid" values being inserted; invalid value translated 0 length string, warning issued, it's not error.

demonstration:

create table foo (gen enum('m','f'))  insert foo (gen) values ('x')  -- warning code : 1265 -- data truncated column 'gen' @ row 1  select gen, char_length(gen) foo;  -- gen  char_length(gen)   -- ---  ---------------- --                     0