3

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

3

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.