Bug #3464 Constraints: support CHECK
Submitted: 14 Apr 2004 0:55 Modified: 23 Jan 2019 16:24
Reporter: Mickael Besson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.0 OS:Windows (Windows NT4)
Assigned to: CPU Architecture:Any

[14 Apr 2004 0:55] Mickael Besson
Description:

It will be good to implement 'between' in create table, like that :

create table CAR ( 
NumCar number(3) CHECK (between 0 and 200)
); 

How to repeat:
[8 Feb 2007 20:23] Sveta Smirnova
Thank you for the reasonable feature request.

I'd say add CHECK constraint.
[6 Nov 2009 16:39] Valeriy Kravchuk
Bug #48599 was marked as a duplicate of this one.
[6 Nov 2009 16:50] Valeriy Kravchuk
Bug #22759 was marked as a duplicate of this one.
[6 Nov 2009 16:53] Valeriy Kravchuk
Bug #28941 was marked as a duplicate of this one.
[6 Nov 2009 17:12] Valeriy Kravchuk
Bug #25660 was marked as a duplicate of this one.
[2 Apr 2010 16:24] Karsten Wutzke
6 years into this bug report and still no signs to add CHECK constraints to MySQL...
[3 Nov 2011 14:16] Javier Domingo
Isn't there any way to make this get implemented? Who decides which features are implemented?
[10 Nov 2011 10:51] Boaz Rymland
Javier Domingo: THE ORACLE!
Sorry. Had to word it out :-)

Have no real idea who decides on bugs but naturally this is in the hands of Oracle corp these days.
[5 Mar 2012 8:07] Petr Kalivoda
As of today, it's almost eight years and yet still no progress to this feature request. Should we expect CHECK constraints sometime during the decade after first filling of a feature request with such importance, or should we just start abandoning MySQL?
[24 Jun 2013 11:16] MySQL Verification Team
bug #69556 is a duplicate
[20 Jun 2014 18:19] Marco El Caro
Ten years later: check constraints are important! Is there the possibility to implement this? Or should I do it?
[12 May 2015 13:40] Christopher Schultz
Another request for this feature. Implementing this with stored procedures is tedious and error prone.
[29 Jul 2015 10:49] Alejandro Laorden Hidalgo
I think this is an important feature theat whatever database that follow the standard ANSI SQL-92 has to implement. 
https://en.wikipedia.org/wiki/SQL-92
[17 Aug 2015 2:46] Paul Weiss
This definitely affects me, too. I may have to switch DBMSs. :-(
[20 Jan 2016 16:56] Biswajit Khandai
It certainly is an important feature to add. It was classified as a feature request in 2007. Why does it take so long for such an important feature ?

Is MySQL dead (in terms of development and maintenance) ?
[20 Jan 2016 17:00] Biswajit Khandai
I suspect the neglect might be partly because it is classified as a Feature Request ?

I wonder if this should be reclassified as a bug (standard-compliance bug) rather than a feature request ?
[20 Jan 2016 17:14] Morgan Tocker
CHECK constraints can not happen before the native data dictionary project is complete.  Please see our updates on the MySQL Server team blog:

http://mysqlserverteam.com/a-new-data-dictionary-for-mysql/
http://mysqlserverteam.com/a-preview-on-lab-release-with-new-data-dictionary-in-mysql/
[20 Jan 2016 20:12] Biswajit Khandai
Morgan Tocker,
Thanks for responding.

Data Dictionary improvement is nice, but shouldn't the CHECK feature be reclassified as a "bug" or be given a higher priority somehow ?

Otherwise, I am afraid, this might not get picked up in another 10 years :-(
[20 Jan 2016 20:53] Morgan Tocker
Biswajit Khandai,

This is a feature request.  It represents a change of behavior and additional functionality.
[22 Dec 2016 13:53] Erman Müftüoğlu
Hi Morgan,

You said that "CHECK constraints can not happen before the native data dictionary project is complete" and MySQL 8.0 milestone release is available now. So i guess that data dictionary project is completed. Do you have any plan for this feature implementation ?
[22 Dec 2016 14:55] Morgan Tocker
Please see:
http://mysqlserverteam.com/mysql-8-0-data-dictionary-status-in-the-8-0-0-dmr/

The data dictionary is still in development.
[3 Jan 2017 0:44] Kyler Jensen
Please implement this feature. As an additional note, it should be considered a bug since the CONSTRAINT ... CHECK syntax is parsed, but simply ignored. If this feature is not implemented then the syntax should not be parsed. Obviously the most appropriate solution would be to implement the feature. Another solution might be simply using the CONSTRAINT ... CHECK syntax as shorthand for creating a trigger.
[18 Jan 2017 10:04] Abduhl Rammann
I do not understand why the new data dictionary is strictly necessary. As  Kyler Jensen mentioned, you can already have the same effect as CHECK constraints in MySQL by defining triggers as described here:
http://blog.christosoft.de/2012/08/mysql-check-constraint/
So supporting the CHECK constraint could just mean creating and dropping triggers internally. As triggers are more powerful than CHECK constraints, every CHECK constraint should be possible to do with a trigger. Or am I missing something?

Anyway, using triggers might be a workaround for someone reading this.
[23 Jan 2019 16:24] Paul DuBois
Posted by developer:
 
Fixed in 8.0.15.

Previously, MySQL permitted a limited form of CHECK constraint
syntax, but parsed and ignored it. MySQL now implements the core
features of table and column CHECK constraints, for all storage
engines. Constraints are defined using CREATE TABLE and ALTER TABLE
statements.