patternMinor
Dropping a CHECK in mariadb?
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:
Here's the reverse/rollback plan:
What I am confused is how do I reverse the CHECK?
Thank you.
UPDATE:
I got error
when I try to run add constraint:
I use mariadb 10.2.8 if that matters
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:
Then you may freely remove it by name:
If you forget to assing the name you may to see it in
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.