Bug #25660 CHECK constraints in Create Table
Submitted: 17 Jan 2007 1:45 Modified: 6 Nov 2009 17:12
Reporter: Sahaya Thivearaj Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:server version: 5.0.27-community OS:Microsoft Windows (Windows XP)
Assigned to:

[17 Jan 2007 1:45] Sahaya Thivearaj
Description:
Hi,

I am trying to create table with CHECK constraints, but it creates the table without CHECK constrains even without warning.
Regards
Raj

How to repeat:
CREATE TABLE `usermaster` (
  `userNo` mediumint(9) NOT NULL auto_increment,
  `userId` varchar(20) NOT NULL,
  `userDetail` varchar(200) NOT NULL,
  `userPassword` varchar(20) NOT NULL,
  `userEmail` varchar(100) NOT NULL,
  `activeFlag` varchar(1) NOT NULL default 'Y',
  PRIMARY KEY  (`userNo`),
  UNIQUE KEY `userId` (`userId`),
  UNIQUE KEY `userEmail` (`userEmail`),
  CHECK (INSTR(`userEmail`,'@') >= 1)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
[17 Jan 2007 2:02] Miguel Solorzano
Thank you for the bug report. Indeed this a limitation documented:

http://dev.mysql.com/doc/refman/5.0/en/create-table.html 

"The CHECK  clause is parsed but ignored by all storage engines"
[17 Jan 2007 2:10] Sahaya Thivearaj
Thanks. Is there any way to force CHECK constraints in MySQL?

If no way then before insert or update triggers are the best place to put this check? Please advise.
[6 Nov 2009 15:57] Karsten Wutzke
Please MySQL implement the CHECK CONSTRAINT clause. We really need these basics before anything else. ENUM isn't a replacement for checks. Check constraints improve data quality when used correctly.

Thus, the severity should be S2! Checks aren't just a fancy extension, they're vital.

It's almost 3 years since the issue was reported and still no movement...

(I will have a look at other DBMS products like PostgreSQL or Sybase, because I really need check constraints.)
[6 Nov 2009 17:12] Valerii Kravchuk
This is a duplicate of Bug #3464.