1

I'm trying to add a JSON_VALID constraint to a column in a table on MariaDB v10.3.27 - and keeps giving me errors.

I tried the code I found in MariaDB documentation:

ALTER TABLE table_name
CONSTRAINT CHECK(JSON_VALID(column_name));

returning the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CONSTRAINT CHECK

and

ALTER TABLE table_name
ADD CONSTRAINT CHECK(JSON_VALID(column_name));

returning the error: 1 errors were found during analysis. This option conflicts with "ADD". (near "CHECK" at position 39)

Do you know how to solve this - and add the JSON constraint to the column?

Alexandru Cristian
  • 382
  • 5
  • 16
  • The first one is not a mysql error message, this appears to have come from your IDE. Run the first sql and see what happens. The second one is clearly incorrect syntax with two ADD keywords. – Shadow Jan 28 '22 at 12:06
  • I had a transcription typo from phpMyAdmin - I edited the question, the errors are still there. – Alexandru Cristian Jan 28 '22 at 12:13
  • 1
    The 2nd error is still not a mariadb error message, it comes from phpmyadmin. Run it as is. – Shadow Jan 28 '22 at 13:17
  • 1
    There should be no problem for the second DDL statement. The [demo](https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=be061b782c313a04dad8ce7141954678) is in Mariadb 10.3.32, and this function exists since the version 10.2.3 – Barbaros Özhan Jan 28 '22 at 21:39
  • 1
    Solved - Thank @BarbarosÖzhan for taking the time to create the fiddle! The problem was that **the table had to be emptied before JSON_VALID constraint could be introduced** - even if it already contained only JSON data. – Alexandru Cristian Jan 29 '22 at 12:26

1 Answers1

1
ALTER TABLE table_name
ADD CONSTRAINT CHECK(JSON_VALID(column_name));

this statement worked for me from phpMyAdmin with no empty table but after fixing JSON objects manually even put numeric and empty values between double quotations "" and MariaDB version was 10.3.34-MariaDB.

Mohannd
  • 1,288
  • 21
  • 20