7 JSON Schema

You can create a JSON schema against which to validate the structure and type information of your JSON documents. You can validate data on the fly or do it with a check constraint to ensure that only schema-valid data is inserted in a JSON column.

Most uses of JSON data are schemaless. Applications that use JSON data can then quickly react to changing requirements. You can change and redeploy an application without needing to change the storage schemas it uses.

However, sometimes you might want some JSON data to conform to a schema. You might want to ensure that all data stored in a given column has the structure defined by a schema, or you might want to check whether a given JSON document has such a structure, before processing it.

A JSON schema is a JSON document that respects the JSON Schema standard, which is a Request For Comments (RFC) draft.

JSON schemas can in turn be used to describe or validate other JSON documents. See json-schema.org. A JSON schema specifies the structure and the types of allowed values of JSON data that it considers valid. "Validity" is always with respect to a given schema. ("Well-formedness", on the other hand, just means syntactically correct.)

The JSON schemas supported by Oracle AI Database are self-contained. They cannot include or import other JSON schemas. (If you try to do so, the schema keywords you use for that are simply ignored, as if they were user-defined keywords.)

Note:

Static dictionary views ALL_JSON_DOMAIN_SCHEMA_COLUMNS, DBA_JSON_DOMAIN_SCHEMA_COLUMNS, and USER_JSON_DOMAIN_SCHEMA_COLUMNS record the JSON schema that defines a domain. See ALL_JSON_DOMAIN_SCHEMA_COLUMNS in Oracle AI Database Reference.

This is an example of a simple JSON schema that uses only standard fields (keywords): 

{"type"       : "object",
 "properties" : {"firstName" : {"type"      : "string",
                                "minLength" : 1},
                 "salary"    : {"type"      : "number",
                                "minimum"   : 10000}},
 "required"   : ["firstName"]}

It specifies that a valid document is a JSON object that has a field firstName and optionally a field salary. The object can contain additional fields, besides firstName, which is required and which must be a string of at least one character, and salary, which is optional but if present must be a number at least as large as 10,000.

_________________________________________________________

  • Validating JSON Data with a JSON Schema
    A JSON schema is a JSON object that typically specifies the allowed structure and data typing of other JSON data — its validity with respect to that schema. A typical use of a JSON schema is thus to validate JSON data.
  • JSON Schema Validation With Type Casting To Extended Scalar Values
    If you use VALIDATE CAST in an IS JSON check constraint for a JSON-type column, then data to be inserted can be automatically type-cast to Oracle-specific JSON-language scalar values, to accommodate the JSON schema. For example, an ISO 8601 date string can be converted to a JSON date value.
  • Generating JSON Schemas
    You can generate (create) a JSON schema from an existing set of JSON documents or from other database objects/data.
  • JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE
    The mapping is described that PL/SQL function DBMS_JSON_SCHEMA.describe uses to generate a JSON schema from a database table, view, JSON-relational duality view, object-type instance, collection-type instance (varray or nested table), or domain.
  • Explicitly Declaring Column Check Constraints Precheckable or Not
    When you create or alter a table you can explicitly declare individual column check constraints to be precheckable (or not) outside the database. If any constraint you declare to be precheckable is not actually precheckable then an error is raised.

See Also:

Parent topic: Store and Manage JSON Data

7.1 Validating JSON Data with a JSON Schema

A JSON schema is a JSON object that typically specifies the allowed structure and data typing of other JSON data — its validity with respect to that schema. A typical use of a JSON schema is thus to validate JSON data.

You can validate JSON data against a JSON schema in any of these ways:

  • Use condition is json (or is not json) with keyword VALIDATE and the name of a JSON schema, to test whether targeted data is valid (or invalid) against that schema. The schema can be provided as a literal string or a data use case domain. Keyword VALIDATE can optionally be followed by keyword USING.

    You can use VALIDATE with condition is json anywhere you can use that condition. This includes use in a WHERE clause, or as a check constraint to ensure that only valid data is inserted in a column. Example 7-1 illustrates its use in a WHERE clause.

    When used as a check constraint for a JSON-type column, you can alternatively omit is json, and just use keyword VALIDATE directly. These two table creations are equivalent, for a JSON-type column: 

    CREATE TABLE tab (jcol JSON VALIDATE '{"type" : "object"}');
    CREATE TABLE tab (jcol JSON CONSTRAINT jchk
  CHECK (jcol IS JSON VALIDATE '{"type" : "object"}'));

    When using VALIDATE with condition is json as a check constraint, if the JSON schema specifies that a field in the data to be inserted must satisfy an extendedType requirement of being of an Oracle-specific JSON-language scalar type, such as date, then by default a scalar value that's not of that type causes the insertion to fail, even if the value could be type-cast to the required type.

    But if VALIDATE is used together with keyword CAST, then such type-casting is performed when possible. For example, an input string in a supported ISO 8601 date-time format can be automatically cast to a JSON-language date scalar value. See JSON Schema Validation With Type Casting To Extended Scalar Values.

  • Use a domain as a check constraint for JSON type data. For example: 

    CREATE DOMAIN jd AS JSON CONSTRAINT jchkd
  CHECK (jd IS JSON VALIDATE '{"type" : "object"}');
    CREATE TABLE jtab(jcol JSON DOMAIN jd);

    When creating a domain from a schema, you can alternatively omit the constraint and is json, and just use keyword VALIDATE directly. This domain creation is equivalent to the previous one: 

    CREATE DOMAIN jd AS JSON VALIDATE '{"type" : "object"}';

  • Use PL/SQL function or procedure is_valid in package DBMS_JSON_SCHEMA. You can use the function in SQL queries. It just returns 1 if the data is valid and 0 if invalid. The procedure returns an OUT parameter that indicates whether valid or invalid, and another OUT parameter that returns a JSON object that provides full information: the validity (true or false) and any reasons for invalidity.

    For example, this use of the procedure checks data myjson (JSON) against schema myschema (JSON), providing output in parameters validity (BOOLEAN) and errors (JSON). 

    DBMS_JSON_SCHEMA.is_valid(myjson, myschema, validity, errors);

  • If you use procedure (not function) is_valid, then you have access to the validation errors report as an OUT parameter. If you use function is_valid then you don't have access to such a report. Instead of using function is_valid, you can use PL/SQL function DBMS_JSON_SCHEMA.validate_report in a SQL query to validate and return the same full validation information that the reporting OUT parameter of procedure is_valid provides, as a JSON type instance. The JSON data accepted by this function as input can be of data type JSON or VARCHAR2 (not CLOB or BLOB).

    For example, this query tries to validate the textual JSON document that is the first argument against the JSON schema that is the second argument, and it returns a validation report as myreport. 

    
SELECT DBMS_JSON_SCHEMA.validate_report('{"name" : "scott",
                                          "role" : "developer"}',
                                        '{"type" : "array"}')
  AS myreport;

  • Use PL/SQL JSON_ELEMENT_T Boolean method schema_validate(). It accepts a JSON schema as argument, of type JSON, VARCHAR2, or JSON_ELEMENT_T.

    For example, if d is a PL/SQL instance of type JSON_ELEMENT_T then this code returns a BOOLEAN value that indicates whether the JSON data d is valid (TRUE) or not (FALSE) against the JSON schema passed as argument. That is, it checks whether the data is a JSON object. 

    isvalid := d.schema_validate('{"type" : "object"}');

Note:

JSON-type modifiers provide an easy way to obtain a small subset of the behavior you can provide with a JSON schema.

To constrain a JSON-type column to have only object, only array, or only scalar values, then instead of adding a JSON schema VALIDATE check constraint {"type" : "object"}, {"type" : "array"}, or {"type" : "scalar"}, you can simply define the type of the column as a modified JSON type: JSON(OBJECT), JSON(ARRAY), or JSON(SCALAR), respectively.

For example, these two column definitions are essentially equivalent:

CREATE TABLE tab (jcol JSON (OBJECT));
CREATE TABLE tab (jcol JSON VALIDATE '{"type" : "object"}');

You can combine the JSON-type modifiers — for example, (OBJECT, ARRAY) requires column values to be nonscalar. You can specify the column size, limit stored scalar values to particular types, limit the number of array elements, and limit array elements to a given scalar type.

JSON Schema is itself defined as a JSON schema. That schema defines what the JSON Schema standard allows as a valid JSON schema; that is, it defines what forms of JSON document are JSON schemas. You can use PL/SQL function DBMS_JSON_SCHEMA.is_schema_valid to validate any JSON schema, that is, validate it against the JSON Schema-defining schema.

Static dictionary views DBA_JSON_SCHEMA_COLUMNS, ALL_JSON_SCHEMA_COLUMNS, and USER_JSON_SCHEMA_COLUMNS describe a JSON schema that you can use as a check constraint.

Each row of these views contains the name of the table, the JSON column, and the constraint defined by the JSON schema, as well as the JSON schema itself and an indication of whether the cast mode is specified for the JSON schema. Views DBA_JSON_SCHEMA_COLUMNS and ALL_JSON_SCHEMA_COLUMNS also contain the name of the table owner.

For example, given these table and domain creations, a query of view USER_JSON_SCHEMA_COLUMNS shows the following output. 

CREATE TABLE tab (jcol JSON CONSTRAINT jchk
  CHECK (jcol IS JSON VALIDATE '{"type" : "object"}'));

CREATE TABLE jtab(jcol JSON DOMAIN jd);
CREATE DOMAIN jd AS JSON VALIDATE '{"type" : "object"}';

SELECT * FROM USER_JSON_SCHEMA_COLUMNS;

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME JSON_SCHEMA       CAST_MODE
__________ ___________ _______________ ________________  _________
TAB        JCOL        JCHK            {"type":"object"} false
JTAB       JCOL        SYS_C008617     {"type":"object"} false

Note:

Static dictionary views ALL_JSON_DOMAIN_SCHEMA_COLUMNS, DBA_JSON_DOMAIN_SCHEMA_COLUMNS, and USER_JSON_DOMAIN_SCHEMA_COLUMNS record the JSON schema that defines a domain. See ALL_JSON_DOMAIN_SCHEMA_COLUMNS in Oracle AI Database Reference.

Example 7-1 Validating JSON Data Against a JSON Schema with Condition IS JSON

This query selects only data that validates against the literal JSON schema shown, which requires that field PONumber have a numeric value of at least 0.

This works even if column j_purchaseorder was created without any schema validation check constraint. 

SELECT data
  FROM j_purchaseorder
  WHERE data IS JSON VALIDATE
    '{"type"       : "object",
      "properties" : {"PONumber": {"type"    : "number",
                                   "minimum" : 0}}}'

Related Topics

See Also:

Parent topic: JSON Schema

7.2 JSON Schema Validation With Type Casting To Extended Scalar Values

If you use VALIDATE CAST in an IS JSON check constraint for a JSON-type column, then data to be inserted can be automatically type-cast to Oracle-specific JSON-language scalar values, to accommodate the JSON schema. For example, an ISO 8601 date string can be converted to a JSON date value.

If the JSON schema specifies that a field in the data to be inserted must satisfy an extendedType requirement of being of a particular Oracle-specific scalar type, then by default a scalar value that is not of that type causes the insertion to fail, even if the value could be type-cast to the required type. But if VALIDATE is used together with keyword CAST, then such scalar type-casting is performed when possible.

Example 7-2 JSON Schema Validation With Type Casting In an IS JSON Check Constraint

-- Constrain dataOfBirth to be of scalar type date.
CREATE TABLE mytable (
  jcol JSON VALIDATE
       '{"type"       : "object",
         "properties" : {"dateOfBirth" : {"extendedType" : "date"}}}');

-- Try to insert dataOfBirth field with ISO date string.
INSERT INTO mytable VALUES ('{"dateOfBirth" : "2018-04-11"}');
ERROR at line 1: 
ORA-40875: JSON schema validation error

If the table is instead created with keyword CAST then the INSERT succeeds: 

CREATE TABLE mytable (
  jcol JSON VALIDATE CAST
       '{"type"       : "object",
         "properties" : {"dateOfBirth" : {"extendedType" : "date"}}}');

INSERT INTO mytable VALUES ('{"dateOfBirth" : "2018-04-11"}');

1 row created.
-- Query with item-method type() shows the value is a DATE.
SELECT d.jcol.dateOfBirth.type() FROM mytable d;
D.JCOL.DATEOFBIRTH.TYPE()
-------------------------
date

Related Topics

Parent topic: JSON Schema

7.3 Generating JSON Schemas

You can generate (create) a JSON schema from an existing set of JSON documents or from other database objects/data.

If you generate a JSON schema from an existing set of JSON documents, the schema is a hierarchical JSON data guide: a JSON document with fields (JSON Schema and Oracle-specific) that describe the fields commonly found in the documents.

In general, a data guide serves as a guide to understanding the structure of an existing set of JSON documents. As generated, it is typically not appropriate for validation purposes, but it can serve as the basis for a manually defined schema to be used for validating.

See Data-Guide Formats and Ways of Creating a Data Guide and Table 24-2.

Instead of generating a JSON schema from a set of JSON documents, you can generate it from other database data. To do this you use PL/SQL function DBMS_JSON_SCHEMA.describe, passing it any of the following to define the schema:

  • An existing relational table, view, or JSON-relational duality view. It corresponds to an object in the JSON schema. See Table 7-1 for the mapping from a database table, view, or duality view to a JSON schema.

    The generated schema is not dependent on the table, view, or duality view. The definition of that database object is used only when the schema is generated; later changes to the object definition have no effect on the schema.

  • An existing SQL user-defined object-type instance or collection-type instance (a varray or a nested table). An object-type instance corresponds to an object in the JSON schema. A collection-type instance corresponds to a JSON array. See Table 7-1 for the mapping from a database object-type or collection-type instance to a JSON schema.

    The generated schema is not dependent on the object or collection type. The definition of the type is used only when the schema is generated; later changes to the type definition have no effect on the schema.

    If the type of an object-type instance to be described is a subtype of another object type then the generated schema (description) includes all fields that correspond to attributes inherited from the supertype.

  • A data use case domain.

    You can use a domain to indicate the intended use of data of a given type. A domain specification can include a data type, a default value, a collation specification, check constraints, display format, intended ordering, and domain-description metadata in JSON format. A domain does not define a subtype — it does not, itself, restrict the operations that can be performed on the data type that it informs.

    For example, a domain can specify that a given VARCHAR2 column contains email addresses. It can impose relevant usage constraints and validation rules as check constraints.

  • A SQL synonym for a SQL table, view, object type, collection type, domain, or duality view.

    The resulting JSON schema is the same as what would be generated from the table, view, object type, collection type, domain, or duality view.

For example, given table mytable, created with keyword CAST in Example 7-2, if created in database schema john then this is the JSON schema returned by DBMS_JSON_SCHEMA.describe: 

{"title"        : "MYTABLE",
 "dbObject"     : "JOHN.MYTABLE",
 "type"         : "object",
 "dbObjectType" : "table",
 "properties"   : {"JCOL" :
                   {"allOf" : [ {"type"       : "object",
                                 "properties" : {"dateOfBirth" : {"extendedType" : "date"}}} ]}}}

See JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE for the mapping from a database table, view, object type or collection type to a JSON schema.

See Also:

Parent topic: JSON Schema

7.4 JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE

The mapping is described that PL/SQL function DBMS_JSON_SCHEMA.describe uses to generate a JSON schema from a database table, view, JSON-relational duality view, object-type instance, collection-type instance (varray or nested table), or domain.

Generating a schema from a database synonym is the same as generating it from the synonymous database object. For example, a JSON schema generated from a synonym of a table is the same as a schema generated directly from that table.

Table 7-1 specifies the mapping of the general properties of a database object (a table, view, JSON-relational duality view, object-type instance, or collection-type instance) to JSON Schema fields.

The Oracle-specific JSON Schema fields are dbAssigned, dbColumn, dbConstraintExpression, dbConstraintName, dbDomain, dbFieldProperties, dbForeignKey, dbGenerated, dbNoPrecheck, dbObject, dbObjectProperties, dbObjectType, dbPrimaryKey, dbUnique, extendedType, sqlPrecision, sqlScale, and title.

For a table, regular view, or JSON-relational duality view, schema field dbObjectType has value "table", "view", or "dualityView", meaning that the schema was derived (generated) from a table, regular view, or duality view, respectively. Field type has value "object", meaning that the schema expects valid JSON data to be a JSON object.

For an object-type or collection-type instance, schema field dbObjectType has value "type", meaning that the schema was derived from a user-defined database data-type instance. Field type has value "object" or "array", meaning that the schema was derived from an object-type instance or a collection-type instance, respectively, and that the schema expects valid JSON data to be a JSON object or array, respectively.

For a data use case domain, schema field dbObjectType has value "domain", meaning that the schema was derived from a domain.

For the columns of a table, view, or duality view, and the attributes of an object-type instance, schema field properties has as its value a JSON object whose field names correspond to the column or attribute names. The value of each such schema field is a JSON object whose fields, together, specify the JSON values allowed as valid — a value that corresponds to the column or attribute value. For an object-type instance: if the object type is a subtype of another object type then the value of properties includes all fields that correspond to attributes inherited from the supertype.

Columns with a NOT NULL constraint correspond to mandatory fields in the data that is valid according to the generated JSON schema; these column names are the elements of the array value of schema field required. The names of primary-key columns and unique columns are the elements of the array value of schema field dbPrimaryKey and schema field dbUnique, respectively.

A schema describing a table includes a subschema for each column that has a check constraint for which there is known to be a corresponding JSON schema, provided that constraint is not declared NOPRECHECK in the table definition. The included subschema is the corresponding JSON schema — it describes the data that's allowed in the column

An application can use this column-description schema to validate data to be stored in the column before sending it to the database. If that application data is already in the form of JSON then it can use the JSON schema directly to perform this precheck. Otherwise, it can use it as a declarative specification (description) of what needs to be checked.

Any column check constraint that the database has determined cannot be prechecked (that is, has no corresponding JSON schema), or that has been declared NOPRECHECK in the table definition, is instead listed in the (array) value of schema field dbNoPrecheck.

When you use CREATE TABLE or ALTER TABLE the database automatically determines whether column check constraints are known to be precheckable. For constraints created or last altered prior to Oracle Database 23ai, the precheckability is unknown, so the output of function describe includes no JSON schema for such a constraint, nor does it list the constraint in array dbNoPrecheck.

Known precheckability of column check constraints is also recorded in column PRECHECK of static dictionary views ALL_CONSTRAINTS, DBA_CONSTRAINTS, and USER_CONSTRAINTS. The column value is PRECHECK if the constraint has been determined to be precheckable, NOPRECHECK if it has been determined or declared manually not to be precheckable, and NULL if no determination or declaration has yet been made. By default, the value is thus NULL for check constraints created prior to Oracle Database Release 23ai.

For the elements of a collection-type instance, schema field items has as value a JSON object whose fields, together, specify the JSON values allowed for each element of the JSON array — a value that corresponds to values allowed for the collection elements. Schema field maxItems specifies the maximum number of elements for the JSON array.

Table 7-1 JSON Schema Fields Derived From Properties of a Database Object

Field (Keyword) Value Description

dbAssigned

Oracle-specific.

Whether or not a field in a JSON document supported by a JSON-relational duality view is assigned by the database on insert (unless already present).

Examples include providing a default field value and providing a field value from a sequence of unique numbers.

Boolean-valued: if the field is database-assigned then true; otherwise false.

dbColumn

Oracle-specific.

Name of the referenced column.

dbConstraintExpression

Oracle-specific.

SQL expression defining a check constraint that has no equivalent JSON schema or that has been declared NOPRECHECK in the table definition. See dbNoPrecheck.

dbConstraintName

Oracle-specific.

 Name of a check constraint that has no equivalent JSON schema or that has been declared NOPRECHECK in the table definition. See dbNoPrecheck.

dbDomain

Oracle-specific. Used only for annotation, not for validation.

Fully qualified name of the associated domain. Present only for a column that is associated with a domain.

dbFieldProperties

Oracle-specific.

Information about which operations are allowed on a JSON-relational duality view column or a field in a JSON document supported by the view.

The value is an array, with these possible elements: "delete", "insert", "update", and "check".

The descriptions of the elements are the same as for the dbObjectProperties elements of the same name, but for dbFieldProperties the elements apply only to the given column or its corresponding document field.

dbForeignKey

Oracle-specific. Used only for annotation, not for validation.

A JSON array whose elements specify objects in the JSON value to be validated that correspond to foreign-key columns of the table or view.

dbGenerated

Oracle-specific.

Whether the value of a field in a JSON document supported by a JSON-relational duality view is generated/computed, instead of coming directly from a single column.

An example is a field totalCompensation, whose value is computed by adding the values of columns salary and bonus.

Boolean-valued: if the field is generated/computed then true; otherwise false.

dbNoPrecheck

Oracle-specific.

Array of objects for check constraints that have no equivalent JSON schema or that have been declared NOPRECHECK in the table definition.. The fields in each object are dbConstraintName and dbConstraintExpression.

Whether a given check constraint is precheckable is also available from column PRECHECK of static dictionary views ALL_CONSTRAINTS, DBA_CONSTRAINTS, and USER_CONSTRAINTS.

dbObject

Oracle-specific.

Fully qualified name of the database object.

dbObjectProperties

Oracle-specific.

Information about which operations are allowed on a JSON-relational duality view as a whole. The value is an array, with these possible elements:

  • "check" — If present then one or more fields of a document supported by the view contribute to the calculation of the ETAG value (value of field etag of the object that is the value of document field _metadata).

  • "delete" — If present then allow deletion of an entire top-level JSON object from the view definition using standard DELETE syntax.

  • "insert" — If present then allow insertion of an entire top-level JSON object into the view definition using standard INSERT syntax.

  • "update" — If present then allow all of these operations:

    • Update entire top-level JSON objects.

    • Update fields of existing objects.

    • Insert new members into existing objects.

    • Delete members from existing objects.

dbObjectType

Oracle-specific. Used only for annotation, not for validation.

The type of the database object that the schema is derived from: "table", "view", "dualityView", "type", or "domain".

dbPrimaryKey

Oracle-specific. Used only for annotation, not for validation.

A JSON array whose elements name fields in the JSON value to be validated that correspond to primary-key columns of the table or view.

dbUnique

Oracle-specific. Used only for annotation, not for validation.

A JSON array whose elements name the fields in the JSON value to be validated that correspond to the unique columns of the table or view.

description

Used only for annotation, not for validation.

A comment describing the JSON value to be validated by the schema — typically its intended purpose or meaning.

extendedType

Oracle-specific.

The JSON-language types specified for the JSON value to be validated by the schema. The value is a string or an array of strings.

The types named by the strings can include the standard types that are supported by standard JSON Schema keyword type. But they can also include the Oracle-specific types "binary", "double", "float", "date", "timestamp", "timestampTz", "ymInterval", and "dsInterval".

If keywords type and extendedType are used together then they must specify compatible types; otherwise no data targeted by the fields is considered valid. (The validity of the schema itself is not affected by such incompatibility.)

For validation provided by Oracle AI Database, JSON-language type compatibility is defined by whether SQL/JSON function json_serialize can convert between instances of the types — see SQL/JSON Function JSON_SERIALIZE.

items

A JSON object that specifies each element of a JSON array.

For a schema derived from a collection type, its fields together specify a JSON value that corresponds to an element in an instance of the collection type.

maxItems

A JSON number that specifies the maximum number of elements allowed in a JSON array.

For a schema derived from a collection type, it is the maximum number of elements allowed for an instance of the collection type.

maxLength

Maximum length, in characters, of the JSON string to be validated.

minLength

Minimum length, in characters, of the JSON string to be validated.

properties

A JSON object whose fields specify the values of the same fields in the JSON object to be validated.

The fields specify data that corresponds to table or view column data or object-type attribute data.

required

A JSON array whose elements name the fields that are required in the JSON value to be validated.

For a schema derived from a table or view, they name the NOT NULL columns of the table or view.

sqlPrecision

Oracle-specific. Used only for annotation, not for validation.

The precision of instances of a JSON-language numeric type (number, double, float) or timestamp type.

sqlScale

Oracle-specific. Used only for annotation, not for validation.

The scale of instances of JSON-language numeric types.

title

Used only for annotation, not for validation.

Oracle-specific use: The name of the database object (table, view, JSON-relational duality view, object type, collection type, or domain) that the schema is derived from.

type

The JSON-language types specified for the JSON value to be validated by the schema. The value is a string or an array of strings.

The types named by the strings include only the standard types (not Oracle-specific types), "null", "boolean", "object", "array", "number", and "string", as well as "integer" which matches any number with a zero fractional part.

See Also:

Parent topic: JSON Schema

7.5 Explicitly Declaring Column Check Constraints Precheckable or Not

When you create or alter a table you can explicitly declare individual column check constraints to be precheckable (or not) outside the database. If any constraint you declare to be precheckable is not actually precheckable then an error is raised.

A precheckable check constraint is one that (1) has an equivalent JSON schema and (2) has not been explicitly declared to not be precheckable.

If a column constraint is precheckable then an application can prevalidate data before sending it to the database. This client-side detection of invalid data can make an application more resilient and reduce potential system downtime.

If an application uses JSON data then it can use the equivalent JSON schema directly to perform the precheck. If not, the schema can serve as a description of the kind of validation that's needed.

When you create or alter a table, its column check constraints are automatically examined to see whether they are precheckable. This information about known precheckability is then made available in two places:

  • The JSON schema produced by PL/SQL function DBMS_JSON_SCHEMA.describe for an existing table.

    The JSON schema that's equivalent to a check constraint is included in the schema that describes the table. Check constraints that are not precheckable are listed in table schema property dbNoPrecheck.

  • Column PRECHECK of static dictionary views ALL_CONSTRAINTS, DBA_CONSTRAINTS, and USER_CONSTRAINTS.

    The view rows list check constraints. The value of column PRECHECK is PRECHECK if the constraint is known to be precheckable, NOPRECHECK if known to not be precheckable, and NULL otherwise.

    NULL indicates that the constraint's precheckability has not yet been determined (set). This is the case by default for constraints created prior to Oracle Database 23ai.

You can explicitly prevent the creation or altering of a table that has a check constraint that is not precheckable. You do this by adding keyword PRECHECK to the constraint in a CREATE or ALTER TABLE statement. If the constraint is not precheckable then an error is raised.

In particular, you can use keyword PRECHECK with ALTER TABLE on column check constraints created before Oracle Database Release 23ai, which introduced automatic determination of precheckability. If no error is raised for a constraint to which you apply keyword PRECHECK, the constraint is known to be precheckable. In that case, PL/SQL function DBMS_JSON_SCHEMA.describe and dictionary views ALL_CONSTRAINTS, DBA_CONSTRAINTS, and USER_CONSTRAINTS are handled as described above for a precheckable constraint.

If you use keyword NOPRECHECK then you are, in effect, declaring that the constraint is not precheckable, which generally means that the data won't be prechecked outside the database. Use of NOPRECHECK doesn't imply that there's no JSON schema equivalent to the constraint, and it doesn't prevent an application from prechecking. It just says not to expect that the data is precheckable.

By default, even if a column constraint is precheckable, and even if data to be inserted in the column is in fact prechecked by an application, the database still uses the check constraint to validate the data on its side. That is, the data is both prevalidated by the app and validated by the database.

If you add keywords DISABLE RELY to a constraint, along with keyword PRECHECK, then the database does not use the constraint to validate the column data, and it doesn't guarantee that the constraint is satisfied. The query optimizer assumes that the constraint is satisfied, so it generates an execution plan that might fail if the data is invalid. Use DISABLE RELY if you want applications alone to be responsible for validating the column data.

Example 7-3 Prechecking Column Constraints

Table employees of sample schema hr includes columns salary and commission_pct, as follows (from describe hr.employees): 

Name           Null?    Type
-------------- -------- -----------
SALARY         NOT NULL NUMBER(8,2)
COMMISSION_PCT NOT NULL NUMBER(2,2)

In order to inform the database that applications expect to be able to precheck these two columns, a developer adds column constraints with keyword PRECHECK: the salary must be at least 2000, and the salary times the commission percentage must be less than 6000. 

ALTER TABLE employees
  ADD CONSTRAINT min_salary CHECK (salary >= 2000) PRECHECK;

ALTER TABLE employees
  ADD CONSTRAINT max_bonus CHECK ((salary * commission_pct) < 6000) PRECHECK;

Check constraint max_bonus is not precheckable, because it has no equivalent JSON schema. As a result, the constraint creation raises an error. 

ORA-40544: CHECK expression of 'MAX_BONUS' constraint not possible to use as PRECHECK condition

Constraint max_bonus has no corresponding JSON schema, which by definition means that it's not "precheckable". An application can nevertheless prevalidate the salary and commission percentages of a row that it wants to insert or update, to ensure that the constraint is satisfied. It just can't do so using a JSON schema that's equivalent to the SQL expression (salary * commission_pct) < 6000.

PRECHECK needs to be removed from the constraint creation, for it to be accepted (no error raised): 

ALTER TABLE employees
  ADD CONSTRAINT max_bonus CHECK ((salary * commission_pct) < 6000);

After defining the check constraints, the output of DBMS_JSON_SCHEMA.describe for table hr.employees includes the JSON schema that's equivalent to constraint min_salary, and the array value of field dbNoPrecheck contains an entry for constraint max_bonus. 

SELECT DBMS_JSON_SCHEMA.describe('EMPLOYEES');
{"title"         : "EMPLOYEES",
 "dbObject"      : "HR.EMPLOYEES",
 "dbObjectType"  : "table",
 ...
 "dbNoPrecheck"  : [ {"dbConstraintName"       : "MAX_BONUS",
                      "dbConstraintExpression" :
                        "(salary * commission_pct) < 6000"} ],
 ...
 "properties"    : {...
                    "SALARY" : {"extendedType" : "number",
                                "allOf"        : [ {"exclusiveMinimum" : 2000} ]}
                    ...
                   }}

See Also:

Parent topic: JSON Schema