[MDEV-13916] Enforce check constraint on JSON type Created: 2017-09-27  Updated: 2019-02-15  Resolved: 2019-02-13

Status: Closed
Project: MariaDB Server
Component/s: JSON
Fix Version/s: 10.4.3

Type: Task Priority: Major
Reporter: Ian Gilfillan Assignee: Michael Widenius
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-9144 JSON data type Closed
relates to MDEV-13313 JSON type alias is insufficiently com... Closed

 Description   

Since there's no context where someone will want a JSON type without the constraint, and excluding it causes compatibility problems with MySQL, why not map

CREATE TABLE t (j JSON);

to

CREATE TABLE t (
 
  j JSON 
  CHECK (JSON_VALID(j))
 
);



 Comments   
Comment by Sergei Golubchik [ 2017-09-27 ]

I considered that in MDEV-13313. To quote

auto-adding a constraint is questionable. An unexpected constraint in the table definition, performance implications, strange errors if one manually specifies some constraint for this column.

that's why I haven't done it. Basically, I can see some users being for and some being against this feature and I cannot say what approach will work best for them all.

Comment by Michael Widenius [ 2019-02-13 ]

Talked with Sergei and we agree to do this with two small changes:

  • The resulting field type will be TEXT, not JSON as we don't yet have a JSON type
  • MariaDB will add an automatic constraint JSON_VALID(field_name) for all JSON fields that doesn't already have a constraint.
    This is to avoid getting things like 'JSON_VALID(field_name) AND JSON_VALID(field_name)' in the check constraint. This is a small
    restriction and one can easily go around it by using table level constraint for "field_name"
Comment by Michael Widenius [ 2019-02-13 ]

Pushed into 10.4

Generated at Thu Feb 08 08:09:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.