Mariadb – Dropping a CHECK in mariadb

mariadbmariadb-10.2

I have a schema migration plan to add json column to mariadb 10.2.
But I am required to have a reverse plan.
Here's the forward plan:

ALTER TABLE `mydb`.`table1` 
ADD COLUMN jsonf JSON DEFAULT NULL;

ALTER TABLE `mydb`.`table1`
ADD CHECK(JSON_VALID(jsonf)) ;

Here's the reverse/rollback plan:

ALTER TABLE `mydb`.`table1`
DROP COLUMN jsonf;

DROP CHECK ?

What I am confused is how do I reverse the CHECK?
Thank you.

UPDATE:

I got error

 Kernel error: Error( 4025 ) 23000: "CONSTRAINT `jsonf_is_json` failed for `mydb`.`#sql-8f8_d0c5c1`"

when I try to run add constraint:

ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT jsonf_is_json CHECK(JSON_VALID(jsonf));

I use mariadb 10.2.8 if that matters

Best Answer

You must assign a name to the CHECK constraint during add:

ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT check_json_validity CHECK(JSON_VALID(jsonf));

Then you may freely remove it by name:

ALTER TABLE
DROP CONSTRAINT check_json_validity;

If you forget to assing the name you may to see it in SHOW CREATE TABLE output.

Related Question