CHECK mysql not working (no error)

Question

CREATE TABLE batsman
(
  InningId int NOT NULL,
  PlayerId int NOT NULL,
  BatsmanOrder int(2) NOT NULL,
  BatScore int NOT NULL default 0,
  Balls int NOT NULL default 0,
  sixes int NOT NULL default 0,
  fours int NOT NULL default 0,
  `out` varchar(10) NOT NULL, /*Foreign*/
  catcher int, /*Foreign*/
  bowler int, /*Foreign*/
  Primary Key(InningId, PlayerId),
  Foreign Key (PlayerId) references player(Id),
  Foreign Key (InningId) references inning(Id),
  CHECK (`out` in ("Caught", "Run Out", "Not Out", "Bowled", "lbw"))
  ) ENGINE=INNODB;

I'm using MySql and I cannot get my Check to work it seems to simply not work i don't get a error or anything. It may be something to do with the '' tags around the out as out is a key word!

Answer 1

Quoting the 12.1.17. CREATE TABLE Syntax of the MySQL manual :

The CHECK clause is parsed but ignored by all storage engines

Maybe this explains that ?

Purely as a suggestion, maybe a possible solution for you would be to use either :

• 10.4.4. The ENUM Type for your column, if it must only contain a few values -- note there are drawbacks to that approach, as an enum doesn't exactly work like a varchar.
• a Trigger to prevent invalid data to be inserted

But those are just wild propositions that I didn't test in this kind of situation.

Answer 2

MySQL does not support check constraints.

You can use a trigger to do the check instead. Here's an example: http://forge.mysql.com/wiki/Triggers#Emulating_Check_Constraints