Bug #60399 CHECK constraint not working
Submitted: 8 Mar 2011 21:11 Modified: 8 Mar 2011 21:41
Reporter: Arthur Yeo Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.5.8 OS:Windows (Windows 7 - 64bit)
Assigned to: CPU Architecture:Any
Tags: check, constraints, missing

[8 Mar 2011 21:11] Arthur Yeo 
Description:
It looks like the CHECK constraints in the CREATE TABLE is not implemented in the MySQL Server itself.
Syntactically, the parser will accept the CHECK constraint but internally, it is not checking it.

How to repeat:
use test;
DROP TABLE IF EXISTS `test`.`mytable` ;

CREATE  TABLE IF NOT EXISTS `test`.`mytable` (
  `AccountId` INT NOT NULL ,
  `SomeField` DECIMAL(18,5) NOT NULL DEFAULT 0.00 ,
  PRIMARY KEY (`AccountId`),
  check (SomeField > 0)
)
ENGINE = InnoDB;

insert into mytable (AccountId, SomeField) values (2, -1);

select * from mytable;