Constraint Checks that actually checked the constraints were introduced last year with MySQL 8.0.16 and they really do help you keep bad data out of your database.  I have found them very handy in the past year but today I spotted a question on a website about how to remove a Constraint Check.

What is a Constraint Check?It is an integrity check.  In the following example a constraint is set up to make sure the calues of the column 'is' are greater than one hundred.


CREATE TABLE c1 (id INT NOT NULL, 
   CONSTRAINT id_gt_100 CHECK (('id' > 100))
);

A simple test with a value in range runs perfectly fine.

INSERT INTO c1 (id) VALUES (10000);
Query OK, 1 row affected (0.0037 sec)

But you will receive an error if the value of 'is' is less than  100.

INSERT INTO c1 (id) …

i was working on some example code for using check constraints and was fairly happy with my first bit of of code.

CREATE TABLE c1 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0)
);

it worked well and did what I wanted which was to reject data where the value for x was not one or greater. MySQL has allowed you to have constraint checks for many years but they only came to life in 8.0.16.  Before that version the checks were simply ignored.

So I was writing some more demo code and smacked into what I thought was an odd error.

CREATE TABLE c2 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0), 
                   CONSTRAINT …