Dropping a CHECK in mariadb?

Question

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

score 3 · Answer 1 · answered Feb 04 '20 at 12:19

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.

score 2 · Answer 2 · answered Feb 04 '20 at 12:31

You should always (if possible) name your constraints:

CREATE TABLE T ( x int not null );

ALTER TABLE T 
    ADD COLUMN jsonf JSON DEFAULT NULL;

ALTER TABLE T
    ADD CONSTRAINT T_C1 CHECK(JSON_VALID(jsonf)) ; 

ALTER TABLE T DROP CONSTRAINT T_C1;

ALTER TABLE T DROP COLUMN jsonf;

If you have anonymous constraints, you can find them in:

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

Dropping a CHECK in mariadb?

2 Answers2