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!

Pascal MARTIN · Accepted Answer · 2010-03-01 19:35:26Z

2

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.

answered Mar 1, 2010 at 19:35

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Over a year ago

Or maybe a reference to another table with varchar primary key containing the list of allowed values.

FelixM Over a year ago

This is why I avoid using MySQL whenever I can. They are notoriously bad at supporting standard SQL. PostgreSQL is much better in that respect (postgresql.org).

Ike Walker · Accepted Answer · 2010-03-01 20:25:33Z

0

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

answered Mar 1, 2010 at 20:25

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Collectives™ on Stack Overflow

CHECK mysql not working (no error)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related