8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Precheck Constraints using JSON Schema in Oracle Database 23c
In Oracle database 23c we can use the PRECHECK
keyword to mark check constraints as being validated externally by an application. The DBMS_JSON_SCHEMA
package allows us to describe objects in the form of a JSON schema, which can be passed to an application to define how the data should be presented to the database so it is processed without errors.
Related articles.
- JSON Schema in Oracle 23c
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Basic Check Constraints
The PRECHECK
keyword indicates a check constraint is prechecked by the application before the data it is sent to the database. In its default form, the check constraint is still validated in the database.
In this example we create a table with a check constraint set to PRECHECK
. We see invalid data still causes a constraint violation with the PRECHECK
option.
drop table if exists t1 purge; create table t1 ( id number, valid varchar2(1), constraint t1_pk primary key (id), constraint valid_chk check (valid in ('Y','N')) precheck ); insert into t1 (id, valid) values (1, 'B'); * ERROR at line 1: ORA-02290: check constraint (TESTUSER1.VALID_CHK) violated SQL>
We use the ALTER TABLE
command to set the constraint to DISABLE PRECHECK
, which means we are totally reliant on the application to validate the data. This means we can insert invalid data if we fail to manually validate it.
alter table t1 modify constraint valid_chk disable precheck; insert into t1 (id, valid) values (1, 'B'); 1 row created. SQL>
Remember, we can only enable the constraint if the underlying data doesn't violate it, unless we use the ENABLE NOVALIDATE
option. In the following example we use the ALTER TABLE
command to cycle through various constraint settings.
select status, validated, precheck from user_constraints where constraint_name = 'VALID_CHK'; STATUS VALIDATED PRECHECK -------- ------------- -------- DISABLED NOT VALIDATED PRECHECK SQL> alter table t1 modify constraint valid_chk enable novalidate precheck; select status, validated, precheck from user_constraints where constraint_name = 'VALID_CHK'; STATUS VALIDATED PRECHECK -------- ------------- -------- ENABLED NOT VALIDATED PRECHECK SQL> alter table t1 modify constraint valid_chk enable noprecheck; * ERROR at line 1: ORA-02293: cannot validate (TESTUSER1.VALID_CHK) - check constraint violated SQL> truncate table t1; alter table t1 modify constraint valid_chk enable noprecheck; select status, validated, precheck from user_constraints where constraint_name = 'VALID_CHK'; STATUS VALIDATED PRECHECK -------- ------------- -------- ENABLED VALIDATED SQL>
JSON Schema Check Constraints
We can use the PRECHECK
option for check constraints that validate JSON data against a JSON Schema.
In this example we create a table using the JSON
data type, and use a check constraint to validate the JSON data conforms to a specific JSON schema. We are using the PRECHECK
option, but even when we insert valid JSON data we get a JSON Schema violation if it doesn't conform to the JSON Schema defintion.
drop table if exists t2 purge; create table t2 ( id number, json_data json, constraint t2_pk primary key (id), constraint json_data_chk check (json_data is json validate '{ "type" : "object", "properties" : {"fruit" : {"type" : "string", "minLength" : 1, "maxLength" : 10}, "quantity" : {"type" : "number", "minimum" : 0, "maximum" : 100}}, "required" : ["fruit", "quantity"] }') precheck ); insert into t2 (id, json_data) values (2, json('{"fruit":"apple"}')); * ERROR at line 1: ORA-40875: JSON schema validation error SQL>
Here we use the ALTER TABLE
command to set the constraint to DISABLE PRECHECK
, which means we are totally reliant on the application to validate the data matches the JSON Schema. This means we can add valid JSON, which doesn't conform to the JSON Schema definition.
alter table t2 modify constraint json_data_chk disable precheck; insert into t2 (id, json_data) values (3, json('{"fruit":"apple"}')); 1 row created. SQL>
We cycle through the various constraint states as before.
select status, validated, precheck from user_constraints where constraint_name = 'JSON_DATA_CHK'; STATUS VALIDATED PRECHECK -------- ------------- -------- DISABLED NOT VALIDATED PRECHECK SQL> alter table t2 modify constraint json_data_chk enable novalidate precheck; select status, validated, precheck from user_constraints where constraint_name = 'JSON_DATA_CHK'; STATUS VALIDATED PRECHECK -------- ------------- -------- ENABLED NOT VALIDATED PRECHECK SQL> alter table t2 modify constraint json_data_chk enable noprecheck; * ERROR at line 1: ORA-02293: cannot validate (TESTUSER1.JSON_DATA_CHK) - check constraint violated SQL> truncate table t2; alter table t2 modify constraint json_data_chk enable noprecheck; select status, validated, precheck from user_constraints where constraint_name = 'JSON_DATA_CHK'; STATUS VALIDATED PRECHECK -------- ------------- -------- ENABLED VALIDATED SQL>
DBMS_JSON_SCHEMA.DESCRIBE
The DESCRIBE
function in the DBMS_JSON_SCHEMA
package generates a JSON schema describing the referenced object. It supports a variety of objects listed here. The description of the object can be used by an application to validate the data prior to sending it to the database.
We describe the T1
table created earlier. The output includes the table definition, and the check constraint for the VALID
column. We've used the JSON_SERIALIZE
function to pretty print the output, but this is not necessary.
set long 1000000 pagesize 100 column json_schema format a80 select json_serialize( dbms_json_schema.describe( object_name => 'T1', owner_name => 'TESTUSER1') pretty) as json_schema; JSON_SCHEMA -------------------------------------------------------------------------------- { "title" : "T1", "dbObject" : "TESTUSER1.T1", "type" : "object", "dbObjectType" : "table", "properties" : { "ID" : { "extendedType" : "number" }, "VALID" : { "extendedType" : [ "null", "string" ], "maxLength" : 1, "allOf" : [ { "enum" : [ "Y", "N" ] } ] } }, "required" : [ "ID" ], "dbPrimaryKey" : [ "ID" ] } SQL>
In this example we restrict the description to the VALID
column.
set long 1000000 pagesize 100 column json_schema format a80 select json_serialize( dbms_json_schema.describe( object_name => 'T1', owner_name => 'TESTUSER1', column_name => 'VALID') pretty) as json_schema; JSON_SCHEMA -------------------------------------------------------------------------------- { "dbColumn" : "VALID", "extendedType" : [ "null", "string" ], "maxLength" : 1, "allOf" : [ { "enum" : [ "Y", "N" ] } ] } SQL>
We describe the T2
table created earlier. The output includes the table definition, and the check constraint for the JSON_DATA
column, which itself includes the JSON schema defintion.
set long 1000000 pagesize 100 column json_schema format a80 select json_serialize( dbms_json_schema.describe( object_name => 'T2', owner_name => 'TESTUSER1') pretty) as json_schema; JSON_SCHEMA -------------------------------------------------------------------------------- { "title" : "T2", "dbObject" : "TESTUSER1.T2", "type" : "object", "dbObjectType" : "table", "properties" : { "ID" : { "extendedType" : "number" }, "JSON_DATA" : { "allOf" : [ { "type" : "object", "properties" : { "fruit" : { "type" : "string", "minLength" : 1, "maxLength" : 10 }, "quantity" : { "type" : "number", "minimum" : 0, "maximum" : 100 } }, "required" : [ "fruit", "quantity" ] } ] } }, "required" : [ "ID" ], "dbPrimaryKey" : [ "ID" ] } SQL>
In this example we restrict the description to the JSON_DATA
column.
set long 1000000 pagesize 100 column json_schema format a80 select json_serialize( dbms_json_schema.describe( object_name => 'T2', owner_name => 'TESTUSER1', column_name => 'JSON_DATA') pretty) as json_schema; JSON_SCHEMA -------------------------------------------------------------------------------- { "dbColumn" : "JSON_DATA", "allOf" : [ { "type" : "object", "properties" : { "fruit" : { "type" : "string", "minLength" : 1, "maxLength" : 10 }, "quantity" : { "type" : "number", "minimum" : 0, "maximum" : 100 } }, "required" : [ "fruit", "quantity" ] } ] } SQL>
In this example we describe a JSON-relational duality view created in the article here.
set long 1000000 pagesize 200 column json_schema format a80 select json_serialize( dbms_json_schema.describe( object_name => 'DEPARTMENT_DV', owner_name => 'TESTUSER1') pretty) as json_schema; JSON_SCHEMA -------------------------------------------------------------------------------- { "title" : "DEPARTMENT_DV", "dbObject" : "TESTUSER1.DEPARTMENT_DV", "dbObjectType" : "dualityView", "dbObjectProperties" : [ "insertable", "updatable", "deletable", "check" ], "type" : "object", "properties" : { "_metadata" : { "etag" : { "extendedType" : "string", "maxLength" : 200 }, "asof" : { "extendedType" : "string", "maxLength" : 20 } }, "location" : { "extendedType" : [ "string", "null" ], "maxLength" : 13, "dbAnnotations" : [ "update", "check" ] }, "departmentName" : { "extendedType" : [ "string", "null" ], "maxLength" : 14, "dbAnnotations" : [ "update", "check" ] }, "departmentNumber" : { "extendedType" : "number", "sqlPrecision" : 2, "sqlScale" : 0, "dbAnnotations" : [ "check" ] }, "employees" : { "type" : "array", "items" : { "type" : "object", "properties" : { "job" : { "extendedType" : [ "string", "null" ], "maxLength" : 9, "dbAnnotations" : [ "update", "check" ] }, "salary" : { "extendedType" : [ "number", "null" ], "sqlPrecision" : 7, "sqlScale" : 2, "dbAnnotations" : [ "update", "check" ] }, "employeeNumber" : { "extendedType" : "number", "sqlPrecision" : 4, "sqlScale" : 0, "dbAnnotations" : [ "check" ] }, "employeeName" : { "extendedType" : [ "string", "null" ], "maxLength" : 10, "dbAnnotations" : [ "update", "check" ] } }, "required" : [ "employeeNumber" ] } } }, "required" : [ "departmentNumber" ] } SQL>
We could also get the JSON Schema for a JSON-relational duality view from the JSON_SCHEMA
column in the the USER_JSON_DUALITY_VIEWS
view.
For more information see:
- Using PRECHECK to Pre-validate JSON Data
- JSON Schema in Oracle 23c
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...