Surfacing Invisible Business Rules with SQL Assertions & AI

I saw Dimitri's post on SQL Assertions today and it piqued my curiosity. Putting more business rules closer to data is almost always a great idea, as you can now call DML from any technology and rest assured that your core business rules won't get violated.

💡

Learn more about SQL Assertions from the Oracle Documentation

Thus, I decided to test it out. This worked fine on my Always Free ADB, as it has been automatically upgraded to 23.26.1, the required version for this new feature.

Rather than create example assertions, I turned to Codex to do it for me based on data in the EMP table.

Using the following prompt:

using data in emp, create a group of SQL assertions. SQL assertions are new in Oracle, have a look here for how to create them: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-assertion.html

Codex generated the following:

create assertion if not exists emp_ename_not_blank check (not exists (select 1 from emp where ename is null or trim(ename) is null)) enable validate;

create assertion if not exists emp_job_not_blank check (not exists (select 1 from emp where job is null or trim(job) is null)) enable validate;

create assertion if not exists emp_empno_nn check (not exists (select 1 from emp where empno is null)) enable validate;

create assertion if not exists emp_mgr_not_self check (not exists (select 1 from emp where mgr = empno)) enable validate;

create assertion if not exists emp_mgr_exists check (not exists (select 1 from emp where mgr is not null and mgr not in (select empno from emp))) enable validate;

create assertion if not exists emp_dept_exists check (not exists (select 1 from emp where deptno is not null and deptno not in (select deptno from dept))) enable validate;

create assertion if not exists emp_deptno_nn check (not exists (select 1 from emp where deptno is null)) enable validate;

create assertion if not exists emp_sal_positive check (not exists (select 1 from emp where sal is not null and sal <= 0)) enable validate;

create assertion if not exists emp_comm_nonnegative check (not exists (select 1 from emp where comm is not null and comm < 0)) enable validate;

create assertion if not exists emp_sal_upper_bound check (not exists (select 1 from emp where sal is not null and sal > 1000000)) enable validate;

While some of these are interesting (salary upper bound, commission not negative), most of them are duplicative of not null, PK and FK constraints already in place.

Not a problem. I just refined my prompt slightly:

create some assertions that are not copies of things like PK, FK, etc. look at the business rules embedded in the data and create some based on those

This time, I got a much better set of assertions:

create assertion if not exists emp_br_president_rules check ( not exists ( select 1 from emp where job = 'PRESIDENT' and (mgr is not null or comm is not null or deptno <> 10) ) ) enable validate;

create assertion if not exists emp_br_non_sales_no_comm check ( not exists ( select 1 from emp where job <> 'SALESMAN' and comm is not null ) ) enable validate;

create assertion if not exists emp_br_sales_in_dept30 check ( not exists ( select 1 from emp where job = 'SALESMAN' and deptno <> 30 ) ) enable validate;

create assertion if not exists emp_br_salary_band_clerk check ( not exists ( select 1 from emp where job = 'CLERK' and sal is not null and (sal < 800 or sal > 1300) ) ) enable validate;

create assertion if not exists emp_br_salary_band_salesman check ( not exists ( select 1 from emp where job = 'SALESMAN' and sal is not null and (sal < 1250 or sal > 1600) ) ) enable validate;

create assertion if not exists emp_br_salary_band_analyst check ( not exists ( select 1 from emp where job = 'ANALYST' and sal is not null and sal <> 3000 ) ) enable validate;

create assertion if not exists emp_br_required_core_fields check ( not exists ( select 1 from emp where ename is null or job is null or hiredate is null or sal is null ) ) enable novalidate;

Some examples include:

PRESIDENT must have no manager, no commission, and belong to department 10
only SALESMAN can have commission
SALESMAN must be in department 30
salary bands by job (CLERK, SALESMAN, ANALYST)

All of these rules were not obvious to me, and I was doing literally nothing to enforce them otherwise. Thus, it was possible to have a SALESMAN in OPERATIONS, a PRESIDENT with a manager or an out-of-band salary.

Now, with these new assertions in place, I'm able to better enforce my business rules without having to make any changes to my applications - APEX or otherwise.

Even if you don't have Oracle 26ai or want to use SQL Assertions, it may be worth letting an LLM inspect your data and provide some insights as to which patterns exist. Then, you can pick and choose which ones can be converted to a business rule either the traditional way or via SQL Assertions.

Notice: I should mention that in order to generate these insights, I had to share my data with the LLM - specifically, GPT-5.3-Codex. While this approach can surface valuable and unexpected business rules, it’s important to recognize the potential risks involved. To achieve similar results, you’d need to follow the same process, meaning that your organization's data would also have to be shared with an LLM. Many organizations have strict policies that prohibit sharing sensitive or proprietary data with public LLMs, so always exercise caution and ensure compliance with your company’s data-sharing guidelines.

Title photo by Markus Spiske on Unsplash

Surfacing Invisible Business Rules with SQL Assertions & AI

Comments

More from this blog

Second Time Around

Using SQLcl Projects

Using Claude Desktop to Create Dashboards for APEX

Exploring MCP Server for Oracle Database & APEX

Command Palette

Comments

More from this blog