HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Dropping a CHECK in mariadb?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
droppingcheckmariadb

Problem

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

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#258854, answer score: 3

Revisions (0)

No revisions yet.