📋Steps in this guide1/3

Precheck Constraints using JSON Schema in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai we can use the PRECHECK keyword to mark check constraints as being validated externally by an application.

oracle 23configurationintermediate

by OracleDba

16 views

Basic Check Constraints

The 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 . We see invalid data still causes a constraint violation with the option. We use the command to set the constraint to , 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. Remember, we can only enable the constraint if the underlying data doesn't violate it, unless we use the option. In the following example we use the command to cycle through various constraint settings.

Code/Command (click line numbers to comment):

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>

alter table t1 modify constraint valid_chk disable precheck;

insert into t1 (id, valid) values (1, 'B');

1 row created.

SQL>

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 option for check constraints that validate JSON data against a JSON Schema. In this example we create a table using the data type, and use a check constraint to validate the JSON data conforms to a specific JSON schema. We are using the 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. Here we use the command to set the constraint to , 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. We cycle through the various constraint states as before.

Code/Command (click line numbers to comment):

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>

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>

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 function in the 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 table created earlier. The output includes the table definition, and the check constraint for the column. We've used the function to pretty print the output, but this is not necessary. In this example we restrict the description to the column. We describe the table created earlier. The output includes the table definition, and the check constraint for the column, which itself includes the JSON schema defintion. In this example we restrict the description to the column. In this example we describe a JSON-relational duality view created in the article here . We could also get the JSON Schema for a JSON-relational duality view from the column in the the view. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

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>

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>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!