8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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>
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...