Removing Constraint Checks

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) VALUES (10);
ERROR: 3819: Check constraint 'id_gt_100' is violated.

Remembers it is cheaper and easier to keep bad data out of your databases than it is to correct it later.

Stopping the Check

If you like the check but have some reason to turn it off, say to bulk load some data in a situation where you can clean up the upload later, then use ALTER TABLE to turn off enforcement.

ALTER TABLE c1 ALTER CHECK id_gt_100 NOT ENFORCED;
Query OK, 0 rows affected (0.0150 sec)

Records: 0  Duplicates: 0  Warnings: 0
INSERT INTO c1 (id) VALUES (10);
Query OK, 1 row affected (0.0038 sec)

Or you can remove the constraint check.

ALTER TABLE c1 DROP CHECK id_gt_100;
Query OK, 0 rows affected (0.0091 sec)

Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE c1\G
*************************** 1. row ***************************
       Table: c1
Create Table: CREATE TABLE `c1` (
  `id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0005 sec)

Further reading 

MySQL 8.0.16 Introducing CHECK constraint

MySQL Manual 13.1.20.6 CHECK Constraints