Bug #25660 CHECK constraints in Create Table

• Submitted: 17 Jan 2007 1:45
• Modified: 6 Nov 2009 17:12
• Reporter: Sahaya Thivearaj
• Status: Duplicate
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: server version: 5.0.27-community
• OS: Windows (Windows XP)
• CPU Architecture: Any

[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] MySQL Verification Team

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] Valeriy Kravchuk

This is a duplicate of Bug #3464.