MySQL Bugs: #3464: Constraints: support CHECK

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

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:

Thank you for the reasonable feature request.

I'd say add CHECK constraint.

Bug #48599 was marked as a duplicate of this one.

Bug #22759 was marked as a duplicate of this one.

Bug #28941 was marked as a duplicate of this one.

Bug #25660 was marked as a duplicate of this one.

6 years into this bug report and still no signs to add CHECK constraints to MySQL...

Isn't there any way to make this get implemented? Who decides which features are implemented?

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.

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?

bug #69556 is a duplicate

Ten years later: check constraints are important! Is there the possibility to implement this? Or should I do it?

Another request for this feature. Implementing this with stored procedures is tedious and error prone.

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

This definitely affects me, too. I may have to switch DBMSs. :-(

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) ?

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 ?

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/

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 :-(

Biswajit Khandai,

This is a feature request.  It represents a change of behavior and additional functionality.

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 ?

Please see:
http://mysqlserverteam.com/mysql-8-0-data-dictionary-status-in-the-8-0-0-dmr/

The data dictionary is still in development.

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.

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.

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.