27.7.2.3 Limitations on DML Operations

The following limitations or restrictions apply to all data modification operations (INSERT, UPDATE, and DELETE statements) on JSON duality views:

• The LOW_PRIORITY and IGNORE clauses are not supported.

• Data modification operations on an SQL view defined over a JSON duality view are not supported.

The following SQL statements are not allowed on JSON duality views:

• LOAD DATA

• LOAD XML

• REPLACE

In addition, multi-table UPDATE and DELETE statements are not allowed for JSON duality views.

The next few sections provide information about other limitations and restrictions which apply to specific data modification operations on JSON duality views.

Limitations on Insert Operations

Attempting to insert NULL or an empty object is rejected with an error.

Insert operations must not result in any constraint violations. This include NULL, primary key, unique Key, check, and foreign key constraints.

Values for primary key columns must be specified. It is possible in some cases to deduce a primary key column value from a JOIN. If primary key values are not supplied, and cannot be deduced from a join condition, the insert is rejected with an error.

Values for columns other than primary keys may be omitted. In such cases, either the column's default value, if applicable, or NULL is stored in those columns.

When values columns used in the join condition of objects and sub-objects are specified, the values of the columns used in the join condition must be same.

If the value for a column is not specified and it is part of a sub-object's join condition, the value from the other operand is used in its place. In the INSERT statement shown in this example, the value of column t2.f3 is not specified. t2.f3 is used in the join condition for ChildNode, specifying the value as t1.f1. In this case, t2.f3 is copied from t1.f1.

CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT);
CREATE TABLE t2 (f3 INT PRIMARY KEY REFERENCES t1(f1), f4 INT);

INSERT INTO t1 VALUES (1, 2);
INSERT INTO t2 VALUES (1, 200);

CREATE OR REPLACE JSON DUALITY VIEW dv1 
AS
  SELECT JSON_DUALITY_OBJECT(
    WITH(INSERT, UPDATE, DELETE)
    "_id" : f3,
    "f4" : f4,
    "ChildNode" , (SELECT JSON_DUALITY_OBJECT 
                    (WITH(INSERT, UPDATE)
                    "f1" : f1,
                    "f2" : f2
                      ) 
                   FROM t1 WHERE t1.f1 = t2.f3)
) FROM t2;

INSERT INTO dv1 VALUES('{ "f4" : 400, "ChildNode" : { "f1" : 3,  "f2" : 4 } }');

Since, according to the view definition for dv1, the columns used in the join condition should match, the value for t1.f1 is copied from t2.f3. If no value is specified for either column used in the join condition, the insert operation is rejected with an error.

Similarly, if a column used in a join condition is not projected in the JSON duality view, the value for the column which is not projected is copied from other column used in the join condition.

In some cases, for an object, not specifying a complete sub-object is allowed. This is the case if either of the following conditions is true:

• Rows matching the join condition already exist in the sub-object's table

• Skipping the insertion of this sub-object does not violate any table constraints.

When the root object being inserted references only existing sub-objects, then only the root object is inserted.

When the root object being inserted references only some of all existing sub-objects, then only the root object is inserted. Sub-objects which are not specified are not deleted.

When the root object being inserted references existing sub-objects and modifies some columns not part of the table's primary key, the root object is inserted, and any sub-objects are updated.

If an object or sub-object is defined on the same table at any level of the JSON duality view's definition, values for the columns must be sam; if they are not, the operation is rejected with an error.

Inserts of multiple objects are not allowed on JSON duality views.

The following types of INSERT statements are not allowed on JSON duality views:

• Statements using HIGH_PRIORITY or DELAYED

• INSERT ... ON DUPLICATE KEY UPDATE statement

• INSERT ... SELECT statement

Limitations on Update Operations

Updating JSON objects to an empty object or NULL is not allowed. Updates of primary key column values of the root object and sub-objects are not allowed.

Any update operation resulting in a constraint violation is rejected with an error. Such constraints include NULL, primary key, unique key, check, and foreign key constraints.

For update operations, all projected column values must be specified. Any missing sub-objects or elements in sub-objects are deleted.

If an object and its sub-object columns are not modified, base tables are updated.

If an object is modified but sub-object columns are not modified, then only the object's base table is updated.

If both object and sub-object columns are modified, then the base tables for both objects is updated.

If a new sub-object is inserted by the update, then a new row is inserted in the sub-object's table.

If an existing sub-object is missing (or deleted), the row for this sub-object is deleted.

If the deletion of object caused by an update results in a table constraint violation, the update is rejected with an error.

Replacement of a sub-object an existing sub-object in the base table is supported.

If multiple sub-objects are projected from the same table, the same value must be specified for all such sub-objects.

The etag supplied for an update operation must match the etag generated for the same object, then an error must be reported.

Limitations on Delete Operations

A delete operation is rejected with an error if referential constraint fails.

Nested sub-objects of deleted elements are also deleted.

Singleton sub-objects are not deleted.