set serveroutput on;
declare
cursor c is
with staff(name, role) as (
values
('Newton', 'chair'), ('James', 'chair')
, ('Edwards', 'chair'), ('Teller', 'chair')
, ('Smith', 'intern'), ('Green', 'intern')
)
select role, listagg(name, ', ') as role_staff
from staff
group by role;
l_staff_composition varchar2(20) := 'unbalanced';
begin
for r in c loop
dbms_output.put_line(r.role || ': ' || r.role_staff);
end loop;
if l_staff_composition = 'unbalanced' then
dbms_output.put_line('database assertions can help');
end if;
end;
/
SQL assertions are now available in Oracle AI Database 26.1 to enforce complex, multi-table business rules at the schema level using SQL statements. The goal of assertions is to simplify application code by migrating business rules to the database schema. Without assertions, we would have to write code in PL/SQL or in the application layer to ensure that the business rules are enforced.
All groups need a bit of structure to succeed. Sports teams usually have a captain or two co-captains. Development groups require project managers, team leads and quality assurance specialists. Surgical teams can’t work without a surgeon, several nurses and an anesthesiologist in the group. Sales order details can be seen as a group, with certain products being necessary for other products to be in the same order.
In this post I will explore how assertions can help to keep staff roles organized for a hypothetical University Philosophy department. These examples are intended to illustrate some basic categories of assertions that can be used to enforce group composition in the database.
Getting Started
The examples in this post depend on Oracle 26.1 (23.26.1.0 or higher). This release is currently available in the Oracle cloud or using a container image. I used docker compose to create a local container running in an Oracle Virtualbox appliance.
All of the code for this article is in the practicalplsql repostory at github in the group-composition-assertions-1 directory.
The schema used for the examples will need the create assertion privilege granted. For docker, this is done in the init script used to create users.
Where i have used test scripts, the first mention of each test script includes a link to the full script.
Use Case: Composition Rules For University Departments
Some philosophers may instinctively rebel against the idea, but a well structured department can improve the university experience for all concerned. We will explore the use of database assertions to enforce these basic rules for department roles:
- Departments must have teaching faculty.
- Departments need support staff such as administrators and secretaries.
- There should only be one administrator in a department.
- One or two chairpeople are enough to make decisions.
- Staff members should not have more than two roles in a department.
- Departments with interns need research fellows for supervision.
Setup: Create Tables and Debug Procedure
Let’s start by creating tables for departments, roles, staff, and department roles. We can use foreign key constraints to keep the data consistent, with valid staff, roles and departments. The primary key constraint on the department roles table will ensure that no staff member is assigned to a role in the department more than once. Naming all of the constraints will make it easy to tell when a constraint is violated.
create.tables.sql
create domain if not exists domain_entity_name as
varchar2(50) not null
/
create table if not exists univ_depts (
dept_name domain_entity_name,
constraint univ_depts_pk primary key (dept_name)
)
/
create table if not exists univ_roles(
role_name domain_entity_name,
constraint univ_roles_pk primary key (role_name)
)
/
create table if not exists univ_staff(
staff_name domain_entity_name,
constraint univ_staff_pk primary key (staff_name)
)
/
create table if not exists univ_dept_roles(
dept_name domain_entity_name,
role_name domain_entity_name,
staff_name domain_entity_name,
constraint univ_dept_roles_fk_univ_depts
foreign key (dept_name) references univ_depts (dept_name),
constraint univ_dept_roles_fk_univ_roles
foreign key (role_name) references univ_roles (role_name),
constraint univ_dept_roles_staff_name
foreign key (staff_name) references univ_staff (staff_name),
constraint univ_dept_roles_pk
primary key (dept_name, role_name, staff_name)
)
/
Populate the staff and roles tables with sample data for running the examples.
insert.roles_and_staff.sql
begin
insert into univ_roles (role_name)
values
('admin'), ('assistant'), ('secretary')
, ('chair'), ('faculty')
, ('fellow'), ('intern');
insert into univ_staff (staff_name)
values
('Pascal'), ('Descartes'), ('Wittgenstein')
, ('Joyce'), ('Picasso'), ('Newton')
, ('Moore'), ('Russell'), ('James')
, ('Kirk'), ('Pike'), ('Jones');
commit;
end;
/
Create a debug procedure to print the current staffing roles for a department. This will allow us to easily see the details of what happens for a given block of test code.
create.procedure.display_staff.sql
create or replace procedure display_staff(
p_dept in univ_depts.dept_name%type
)
is
cursor c is
select
role_name,
listagg(staff_name, ', ')
within group (order by staff_name) as staff
from univ_dept_roles
where dept_name = p_dept
group by role_name
order by role_name;
begin
dbms_output.put_line(p_dept || ' staff members:');
for r in c loop
dbms_output.put_line(' ' || r.role_name || ': ' || r.staff);
end loop;
end;
/
As we create some assertions to enforce our staffing rules, we will see that there are often multiple ways to write the same assertion. Alternate approaches can thus be evaluated for correctness and performance. (Evaluation of assertion performance is beyond the scope of this article, but is definitely important).
The test scripts are designed to be run many times to support testing different refactorings of the same assertion for correctness.
Assertion: Faculty Is Required
To start with, someone in the department needs to be responsible for actually teaching classes. The ‘faculty‘ role indicates this assignment. Expressed simply, this assertion enforces the rule that:
“all departments must have at least one faculty member“.
In the future when aggregate assertions are available, we could say that: “all departments must have a count of faculty members greater than zero”. Because this is not supported yet, we can use existential assertions to say that: “no department is valid if no faculty are assigned“.
create.assertion.faculty_required.immediate.sql
create assertion if not exists faculty_required check (
not exists (
select 'a department'
from univ_depts d
where
not exists (
select 'has faculty'
from univ_dept_roles f
where
f.dept_name = d.dept_name
and f.role_name = 'faculty')
)
) not deferrable
/
The existential assertion approach also allows us to test the SQL before creating the assertion. We can execute the primary not exists query block as a standalone query to see if it is well formed and returns no results when no department would violate the rule. Isolating the runnable part of the above assertion gives us this SQL statement to test for syntax errors and correct results.
select 'a department'
from univ_depts d
where
not exists (
select 'has faculty'
from univ_dept_roles f
where
f.dept_name = d.dept_name
and f.role_name = 'faculty')
/
no rows selected
Because the assertion requires that a department does not exist that is returned by this query, the fact that we get no rows indicates that the subquery is correct. The query will only return rows for departments that don’t have faculty.
When we look at the alternative syntax for assertions, the universal approach, we will see that the actual SQL statement within the assertion cannot be tested in this manner.
The default for assertions is that they are applied immediately when the triggering SQL statement is executed. This creates a problem for this type of assertion, how can a department be created that has faculty when it takes one statement to create the department and another statement to insert faculty?
Testing the assertion shows the problem with validating the assertion immediately on department creation.
test.assertion.faculty_required.immediate.sql
declare
l_action varchar2(1000) := 'Create Philosophy dept with no faculty';
begin
insert into univ_depts
set dept_name = 'Philosophy';
commit;
dbms_output.put_line('Success - ' || l_action);
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(sqlerrm);
end;
/
ORA-08601: SQL assertion (PRACTICALPLSQL.FACULTY_REQUIRED) violated.
The fix to this problem is to make the assertion deferrable, allowing the department to be created in one statement and faculty to be assigned in another statement prior to committing the transaction. Because we have asserted that valid departments cannot exist until faculty members are assigned, departments can only be created in the scope of a transaction that also assigns faculty.
The assertion has effectively bound the two tables together with an existential dependency. Any assertion that requires rows to exist in more than one table for rows to exist in any of the involved tables could end up in this situation.
We can correct the situation by making the assertion deferrable.
create.assertion.faculty_required.existential.sql
drop assertion if exists faculty_required;
create assertion if not exists faculty_required check (
not exists (
select 'a department'
from univ_depts d
where
not exists (
select 'has faculty'
from univ_dept_roles f
where
f.dept_name = d.dept_name
and f.role_name = 'faculty')
)
) deferrable initially deferred
/
The assertion assures that a department does not exist where there are no faculty assigned, but will defer validation until the transaction is committed. This deferred validation allows us to insert departments with one statement and assign faculty with another statement before committing the transaction.
Testing the assertion by inserting a department without assigning faculty in the same transaction shows that the assertion is working.
Notice that the assertion violation error is no longer the top error in the stack now that the assertion is deferrable. The error reported by SQLERRM no longer displays the assertion error, instead telling us that the transaction was forcibly rolled back. We have to format the error stack in order to see the assertion violation.
test.assertion.faculty_required.sql
declare
l_action varchar2(100) := '***create dept without faculty';
begin
insert into univ_depts
set dept_name = 'Philosophy';
commit;
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line('SQLERRM WILL NOT SHOW DEFERRED ASSERTION ERRORS:');
dbms_output.put_line(' ' || sqlerrm);
dbms_output.put_line('USE FORMAT_ERROR_STACK TO SHOW DEFERRED ERRORS:');
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Error - ***create dept without faculty
SQLERRM WILL NOT SHOW DEFERRED ASSERTION ERRORS:
ORA-02091: transaction rolled back
USE FORMAT_ERROR_STACK TO SHOW DEFERRED ERRORS:
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (PRACTICALPLSQL.FACULTY_REQUIRED) violated.
The assertion is working, and we can see that the error occurred on commit of the insert statement. To insert a department and keep the assertion valid, we need to assign faculty in the same transaction:
declare
l_action varchar2(100) := '***create dept with faculty';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'faculty';
begin
insert into univ_depts
set dept_name = d;
insert into univ_dept_roles(
dept_name, role_name, staff_name)
values
(d, r, 'Wittgenstein')
, (d, r, 'Newton');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***create dept with faculty
Philosophy staff members:
faculty: Newton, Wittgenstein
Adding more faculty members won’t violate the assertion:
declare
l_action varchar2(100) := '**add faculty members';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'faculty';
begin
insert into univ_dept_roles(
dept_name, role_name, staff_name)
values
(d, r, 'Descartes')
, (d, r, 'Jones');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - **add faculty members
Philosophy staff members:
faculty: Descartes, Jones, Newton, Wittgenstein
We can also delete some faculty members without violating the assertion:
declare
l_action varchar2(100) := '**delete some faculty but not all';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'faculty';
begin
delete univ_dept_roles
where
dept_name = d
and role_name = r
and staff_name = 'Descartes';
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - **delete some faculty but not all
Philosophy staff members:
faculty: Jones, Newton, Wittgenstein
If we try to delete all faculty members from the department, the assertion can no longer be validated and we can’t commit the transaction.
declare
l_action varchar2(100) := 'Remove all faculty';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'faculty';
begin
delete univ_dept_roles
where dept_name = d
and role_name = r;
commit;
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Error - Remove all faculty
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (PRACTICALPLSQL.FACULTY_REQUIRED) violated.
At this point, we can delete all faculty and then assign a new faculty member in the same transaction to keep the assertion valid. Or we can delete all faculty and delete the department.
declare
l_action varchar2(100) := 'Remove dept and all faculty';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'faculty';
begin
delete univ_dept_roles
where
dept_name = d
and role_name = r;
delete univ_depts
where dept_name = d;
commit;
dbms_output.put_line('Success - ' || l_action);
end;
/
Success - Remove dept and all faculty
Using exists or not exists is not the only way to write this assertion. The existential assertion checked that “no departments should exist if faculty assignments do not exist“. We can also use the universal assertion syntax approach to check that “all departments should satisfy the test that faculty assignments exist“.
create.assertion.faculty_required.universal.sql
drop assertion if exists faculty_required;
create assertion if not exists faculty_required check (
all (
select dept_name
from univ_depts
) a
satisfy (
exists (
select 'has faculty'
from univ_dept_roles f
where
f.dept_name = a.dept_name
and f.role_name = 'faculty')
)
) deferrable initially deferred
/
Unlike the existential syntax, the all/satisfy correlated syntax of the universal approach cannot be tested for correctness. The all satisfy syntax is not valid sql outside of an assertion. We have to make some modifications in order to test the statement used to create the assertion: convert the all clause into the main query block while preserving the alias and replacing satisfy with where.
--all (
select dept_name
from univ_depts
--) a
a
--satisfy (
where (
exists (
select 'has faculty'
from univ_dept_roles f
where
f.dept_name = a.dept_name
and f.role_name = 'faculty')
)
/
DEPT_NAME
------------
Math
This shows that the assertion will return all valid departments, when ‘valid’ is defined as ‘having faculty’. However, this query alone can’t tell us that some departments are not valid because they will not be shown in the results.
Running the tests again we can see that the universal assertion is working just like the existential assertion did.
@test.assertion.faculty_required.sql
Success - ***clear univ_dept_roles and univ_depts before tests
Error - ***create dept without faculty
SQLERRM WILL NOT SHOW DEFERRED ASSERTION ERRORS:
ORA-02091: transaction rolled back
USE FORMAT_ERROR_STACK TO SHOW DEFERRED ERRORS:
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (PRACTICALPLSQL.FACULTY_REQUIRED) violated.
Success - ***create dept with faculty
Philosophy staff members:
faculty: Newton, Wittgenstein
Success - **add faculty members
Philosophy staff members:
faculty: Descartes, Jones, Newton, Wittgenstein
Success - **delete some faculty but not all
Philosophy staff members:
faculty: Jones, Newton, Wittgenstein
Error - Remove all faculty
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (PRACTICALPLSQL.FACULTY_REQUIRED) violated.
Success - Remove dept and all faculty
With this assertion in place, faculty have to be added with any new department. Create the department and some faculty before we explore the other group composition assertions needed to implement the business rules.
insert.department_with_faculty.sql
declare
l_action varchar2(100) := '***create department with faculty to test more assertions';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'faculty';
begin
insert into univ_depts
set dept_name = d;
insert into univ_dept_roles(dept_name, role_name, staff_name)
values
(d, r, 'Wittgenstein'), (d, r, 'Descartes')
, (d, r, 'Moore'), (d, r, 'Russell')
, (d, r, 'Pascal');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***create department with faculty to test more assertions
Philosophy staff members:
faculty: Descartes, Moore, Pascal, Russell, Wittgenstein
Assertion: Support Staff Are Required
This rule is follows the same pattern as the faculty are required assertion, except that it can be satisfied if any of two different roles (admin or secretary) are assigned for a department at least once.
Like the previous assertion this assertion needs to be created as deferrable, because a valid department cannot be created without assigning support staff. Since we have already created the department with faculty members, we need to add support staff to the department before the new assertion can be compiled successfully.
create.assertion.support_staff_required.sql
begin
insert into univ_dept_roles
set dept_name = 'Philosophy', role_name = 'admin', staff_name = 'Jones';
commit;
end;
/
drop assertion if exists support_staff_required;
create assertion if not exists support_staff_required check (
all (
select d.dept_name
from univ_depts d
) a
satisfy (
exists (
select 'have an admin or secretary'
from univ_dept_roles p1
where
p1.dept_name = a.dept_name
and (p1.role_name = 'admin' or p1.role_name = 'secretary')
)
)
)
deferrable initially deferred
/
Run some tests to confirm that the assertion is working. Start by trying to delete all support staff from the department:
test.assertion.support_staff_required.sql
declare
l_action varchar2(1000) := '***remove all support staff';
d univ_depts.dept_name%type := 'Philosophy';
begin
delete univ_dept_roles
where
dept_name = d
and role_name in ('admin', 'secretary');
commit;
exception
when others then
rollback;
dbms_output.put_line('Errors - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Errors - ***remove all support staff
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (PRACTICALPLSQL.SUPPORT_STAFF_REQUIRED) violated.
The assertion will not allow us to delete all support staff from the department. The only way to change all staff is to delete them and then insert at least one new staff member:
declare l_action varchar2(1000) := '***remove all support staff, insert admin'; d univ_depts.dept_name%type := 'Philosophy';begin delete univ_dept_roles where dept_name = d and role_name in ('admin', 'secretary'); insert into univ_dept_roles set dept_name = d , role_name = 'admin' , staff_name = 'Jones'; commit; dbms_output.put_line('Success - ' || l_action); display_staff(d);end;/Success - ***remove all support staff, insert adminPhilosophy staff members: admin: Jones faculty: Descartes, Moore, Pascal, Russell, WittgensteinAdding a new staff member does not violate the assertion:
declare l_action varchar2(1000) := '***add secretary'; d univ_depts.dept_name%type := 'Philosophy'; begin insert into univ_dept_roles set dept_name = d , role_name = 'secretary' , staff_name = 'Joyce'; commit; dbms_output.put_line('Success - ' || l_action); display_staff(d);end;/Success - ***add secretaryPhilosophy staff members: admin: Jones faculty: Descartes, Moore, Pascal, Russell, Wittgenstein secretary: JoyceNow that the department has multiple support staff (an admin and a secretary), we can delete as many as we want as long as there is still one:
declare
l_action varchar2(1000) := '***delete secretary';
d univ_depts.dept_name%type := 'Philosophy';
begin
delete univ_dept_roles
where
dept_name = d
and role_name = 'secretary'
and staff_name = 'Joyce';
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***delete secretary
Philosophy staff members:
admin: Jones
faculty: Descartes, Moore, Pascal, Russell, Wittgenstein
Finalize the support staff for the department before we work on the next assertion:
declare
l_action varchar2(1000) := '***finalize support staff';
d univ_depts.dept_name%type := 'Philosophy';
begin
delete univ_dept_roles
where dept_name = d and role_name in ('admin', 'secretary');
insert into univ_dept_roles
values
(d, 'admin', 'Pike')
, (d, 'secretary', 'Kirk');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***finalize support staff
Philosophy staff members:
admin: Pike
faculty: Descartes, Moore, Pascal, Russell, Wittgenstein
secretary: Kirk
The assertions requiring faculty and support staff fall into the group composition patterns: “valid groups require at least one member of type x” or “valid groups require at least one member of type x or y“.
Assertion: Only One Administrator
The next business rule requires that a department with an admin should only have one administrator. We can write this assertion verbosely as “a department does does not exist where an admin exists where there also exists another admin“.
create.assertion.limit_one_admin.sql
drop assertion if exists limit_one_admin;
create assertion if not exists limit_one_admin check (
not exists (
select 'a department'
from univ_depts d
where exists (
select 'an admin'
from univ_dept_roles a1
where a1.dept_name = d.dept_name and a1.role_name = 'admin'
and exists (
select 'another admin'
from univ_dept_roles a2
where
a2.dept_name = a1.dept_name
and a2.role_name = a1.role_name
and a2.staff_name > a1.staff_name))
)
)
/
Since we already have Pike as an admin in the Philosophy department, lets test the new assertion by adding a couple more admins:
test.assertion.limit_one_admin.sql
declare
l_action varchar2(1000) := '***add multiple dept admins';
d univ_depts.dept_name%type := 'Philosophy';
a univ_roles.role_name%type := 'admin';
begin
dbms_output.put_line('Staff before ' || l_action);
display_staff(d);
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values (d, a, 'Joyce'), (d, a, 'Picasso');
commit;
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Staff before ***add multiple dept admins
Philosophy staff members:
admin: Pike
faculty: Descartes, Moore, Pascal, Russell, Wittgenstein
secretary: Kirk
Error - ***add multiple dept admins
ORA-08601: SQL assertion (PRACTICALPLSQL.LIMIT_ONE_ADMIN) violated.
The assertion is working as expected. We cannot add multiple admins to a department. The intent of the rule is expressed in the name of the assertion, allowing the error message to clearly indicate what went wrong with the insert.
We really don’t need to look at the department table at all to enforce this assertion, it is really only checking that not more than one admin assignments are in the same department. The assertion can be simplified to only use the univ_dept_roles table:
create.assertion.limit_one_admin.alt.sql
drop assertion if exists limit_one_admin;
create assertion if not exists limit_one_admin check (
not exists (
select 'an admin'
from univ_dept_roles a1
where
a1.role_name = 'admin'
and exists (
select 'another admin'
from univ_dept_roles a2
where
a1.dept_name = a2.dept_name
and a1.role_name = a2.role_name
and a1.staff_name > a2.staff_name)
)
)
/
Running the test code again we see that the simplified assertion works as well:
Staff before ***add multiple dept admins
Philosophy staff members:
admin: Pike
faculty: Descartes, Moore, Pascal, Russell, Wittgenstein
secretary: Kirk
Error - ***add multiple dept admins
ORA-08601: SQL assertion (PRACTICALPLSQL.LIMIT_ONE_ADMIN) violated.
So far our assertions have been concerned with a minimum of one or a maximum of one. What about larger numbers for limits? The next business rule checks for a maximum of two chairpersons in a department.
Assertion: At Most Two Chairs
In order to limit the chair role to no more than two people, an aggregate assertion would be the perfect solution. We could check all assignments to the ‘chair‘ role grouped by department having a count > 2.
create.assertion.limit_two_chairs.aggregate.sql
drop assertion if exists limit_two_chairs;
create assertion if not exists limit_two_chairs check (
not exists (
select 'a department with more than 2 chairs'
from univ_dept_roles r
where r.role_name = 'chair'
group by r.dept_name
having count(*) > 2
)
)
/
Error report -
ORA-08689: CREATE ASSERTION failed
ORA-08661: Aggregates are not supported.
As the error message indicates, in Oracle AI Database 26.1, aggregate assertions are not supported at all. We will need to write the assertion without using any grouping syntax. The existential syntax can express this rule as “no department exists where a chairperson exists and a co-chair also exists and another co-chair exists“. Start with stating the role explicitly:
create.assertion.limit_two_chairs.too_deep.sql
drop assertion if exists limit_two_chairs;
create assertion if not exists limit_two_chairs check (
not exists (
select 'a department'
from univ_depts d
where exists (
select 'a chair'
from univ_dept_roles c1
where
c1.dept_name = d.dept_name
and c1.role_name = 'chair'
and exists (
select 'a co-chair'
from univ_dept_roles c2
where
c2.dept_name = c1.dept_name
and c2.role_name = c1.role_name
and c2.staff_name > c1.staff_name
and exists (
select 'another co-chair'
from univ_dept_roles c3
where
c3.dept_name = c2.dept_name
and c3.role_name = c2.role_name
and c3.staff_name > c2.staff_name)))
)
)
/
Error report -
ORA-08689: CREATE ASSERTION failed
ORA-08712: Query block nesting limit exceeded (maximum allowed level of nesting = 3).
It took too many levels of nesting to state the assertion. The limit for nested exists clauses in existential assertions is three levels. Universal syntax is limited to only two levels of nested exists/not exists clauses.
We can rewrite the assertion with only three levels by combining the concepts of ‘has a chair’ and ‘has a co-chair’ to look at departments with at least “two co-chairs” and check for the existence of “another co-chair”:
create.assertion.limit_two_chairs.sql
drop assertion if exists limit_two_chairs;
create assertion if not exists limit_two_chairs check (
not exists (
select 'a department'
from univ_depts d
where
exists (
select 'has two chairs'
from
univ_dept_roles c1,
univ_dept_roles c2
where
d.dept_name = c1.dept_name
and d.dept_name = c2.dept_name
and c1.role_name = 'chair'
and c1.role_name = c2.role_name
and c2.staff_name > c1.staff_name
and exists (
select 'another co-chair'
from univ_dept_roles c3
where
c3.dept_name = c1.dept_name
and c3.role_name = c1.role_name
and c3.staff_name > c2.staff_name))
)
)
/
To test this assertion, we can start with two chairpersons before attempting to add a third chair:
test.assertion.limit_two_chairs.sql
declare
l_action varchar2(1000) := '***add two co-chairs';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'chair';
begin
delete univ_dept_roles
where dept_name = d and role_name = r;
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values
(d, r, 'Wittgenstein'), (d, r, 'Descartes');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
declare
l_action varchar2(1000) := '***add a third co-chair';
d univ_depts.dept_name%type := 'Philosophy';
r univ_roles.role_name%type := 'chair';
begin
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values
(d, r, 'Russell');
commit;
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Success - ***add two co-chairs
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Russell, Wittgenstein
secretary: Kirk
Error - ***add a third co-chair
ORA-08601: SQL assertion (PRACTICALPLSQL.LIMIT_TWO_CHAIRS) violated.
The assertion works as expected. Just like the assertion regarding multiple admins, this assertion can be revised to only look at the univ_dept_roles table to see that there does not exist three chairpersons assigned to the same department. With multiple joins to the univ_dept_roles table this can be accomplished with no nesting levels at all.
create.assertion.limit_two_chairs.alt.sql
drop assertion if exists limit_two_chairs;
create assertion if not exists limit_two_chairs check (
not exists (
select 'a department with three co-chairs'
from
univ_dept_roles c1,
univ_dept_roles c2,
univ_dept_roles c3
where
c1.dept_name = c2.dept_name
and c2.dept_name = c3.dept_name
and c1.role_name = 'chair'
and c1.role_name = c2.role_name
and c1.role_name = c3.role_name
and c2.staff_name > c1.staff_name
and c3.staff_name > c2.staff_name
)
)
/
Running the test code again we can confirm that this more compact assertion is working correctly.
@test.assertion.limit_two_chairs.sql
Error - ***add a third co-chair
ORA-08601: SQL assertion (PRACTICALPLSQL.LIMIT_TWO_CHAIRS) violated.
With this more compact format, we could write assertions to check larger quantity limits by adding additional joins to the roles table. Checking for no more than two of a role took three joins to the roles table. Checking for no more than n of a role would require n+1 joins to the roles table. This would work for smaller limits, but it rapidly becomes unrealistic as the number being checked grows.
Assertion: At Most Two Staff Roles
The next rule changes the group being evaluated. Instead of the group being a department, the group is essentially a staff person in a department. The rule states that “no staff member should have more than two roles in a department”.
We can use the universal syntax to see that all staff members with two roles in a department satisfy the test that they don’t have a third role in the department.
create.assertion.limit_two_roles.universal.sql
drop assertion if exists limit_two_roles;
create assertion if not exists limit_two_roles check (
all (
--staff members with two roles in department
select r1.dept_name, r1.staff_name, r1.role_name as role_1, r2.role_name as role_2
from
univ_dept_roles r1,
univ_dept_roles r2
where
r1.dept_name = r2.dept_name
and r1.staff_name = r2.staff_name
and r2.role_name > r1.role_name
) a
satisfy (
not exists (
select 'staff member has three roles'
from univ_dept_roles r3
where
a.dept_name = r3.dept_name
and a.staff_name = r3.staff_name
and r3.role_name > a.role_2
)
)
)
/
To test the assertion, we want to be able to add staff members with two roles successfully. Let’s add Picasso as a faculty member and research fellow:
test.assertion.limit_two_roles.sql
set feedback off;
set serveroutput on;
declare
l_action varchar2(1000) := '***add staff with two roles';
d univ_depts.dept_name%type := 'Philosophy';
s univ_staff.staff_name%type := 'Picasso';
begin
delete univ_dept_roles
where
dept_name = d
and staff_name = s;
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values
(d, 'faculty', s), (d, 'fellow', s);
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***add staff with two roles
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
fellow: Picasso
secretary: Kirk
Having two roles doesn’t violate the assertion. Now we can check that adding a staff member with three roles raises an exception:
declare
l_action varchar2(1000) := '***add staff member with three roles';
d univ_depts.dept_name%type := 'Philosophy';
s univ_staff.staff_name%type := 'Joyce';
begin
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values
(d, 'secretary', s), (d, 'assistant', s), (d, 'intern', s);
commit;
dbms_output.put_line('Success - ' || l_action);
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Error - ***add staff member with three roles
ORA-08601: SQL assertion (PRACTICALPLSQL.LIMIT_TWO_ROLES) violated.
The assertion correctly limits staff to only two roles. The same assertion can also be expressed using the existential syntax:
create.assertion.limit_two_roles.existential.sql
drop assertion if exists limit_two_roles;
create assertion if not exists limit_two_roles check (
not exists (
select r1.staff_name, r1.role_name, r2.role_name, r3.role_name
from
univ_dept_roles r1,
univ_dept_roles r2,
univ_dept_roles r3
where
r1.dept_name = r2.dept_name
and r2.dept_name = r3.dept_name
and r1.staff_name = r2.staff_name
and r2.staff_name = r3.staff_name
and r2.role_name > r1.role_name
and r3.role_name > r2.role_name
)
)
/
The test script executes successfully, showing that this syntax also works:
@test.assertion.limit_two_roles.sql
Success - ***add staff with two roles
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
fellow: Picasso
secretary: Kirk
Error - ***add staff member with three roles
ORA-08601: SQL assertion (PRACTICALPLSQL.LIMIT_TWO_ROLES) violated.
As we saw with the two chairpersons rule, this assertion can also be extended to higher limits with additional joins.
Assertion: Interns Must Be Supervised
The last of our business rules asserts a required relationship between the roles of interns and research fellows. Put simply, “departments can only have interns if they also have fellows“. We can use the universal syntax here with a twist, the all clause can also have an internal exists clause:
create.assertion.interns_require_fellows.universal.sql
drop assertion if exists interns_require_fellows;
create assertion if not exists interns_require_fellows check (
all (
--departments with interns
select d.dept_name
from univ_depts d
where
exists (
select 'has interns'
from univ_dept_roles i
where
i.dept_name = d.dept_name
and i.role_name = 'intern')
) a
satisfy (
exists (
select 'research fellow in department'
from univ_dept_roles f
where
f.dept_name = a.dept_name
and f.role_name = 'fellow'
)
)
)
/
We can run several tests to confirm that this assertion is working. We should violate the assertion if we add interns when there are no fellows. (Clear out all fellows and interns before starting the test).
test.assertion.interns_require_fellows.sql
declare
l_action varchar2(1000) := '***add interns without any fellows';
d univ_depts.dept_name%type := 'Philosophy';
f univ_roles.role_name%type := 'fellow';
i univ_roles.role_name%type := 'intern';
begin
dbms_output.put_line('Staff before ' || l_action);
display_staff(d);
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values
(d, i, 'Joyce')
, (d, i, 'Jones');
commit;
dbms_output.put_line('Success - ' || l_action);
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Staff before ***add interns without any fellows
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
secretary: Kirk
Error - ***add interns without any fellows
ORA-08601: SQL assertion (PRACTICALPLSQL.INTERNS_REQUIRE_FELLOWS) violated.
Adding interns and fellows will not validate the assertion. Running the test confirms that we are able to add fellows and interns.
declare
l_action varchar2(1000) := '***add interns and fellows';
d univ_depts.dept_name%type := 'Philosophy';
f univ_roles.role_name%type := 'fellow';
i univ_roles.role_name%type := 'intern';
begin
insert into univ_dept_roles (
dept_name, role_name, staff_name)
values
(d, f, 'Joyce'), (d, f, 'Picasso')
, (d, i, 'Newton'), (d, i, 'James')
, (d, i, 'Jones');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***add interns and fellows
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
fellow: Joyce, Picasso
intern: James, Jones, Newton
secretary: Kirk
Now that we have interns, all of the research fellows cannot be removed.
declare
l_action varchar2(1000) := '***remove all fellows';
d univ_depts.dept_name%type := 'Philosophy';
f univ_roles.role_name%type := 'fellow';
begin
dbms_output.put_line('Staff before ' || l_action);
display_staff(d);
delete univ_dept_roles
where
dept_name = d
and role_name = f;
commit;
dbms_output.put_line('Success - ' || l_action);
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Staff before ***remove all fellows
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
fellow: Joyce, Picasso
intern: James, Jones, Newton
secretary: Kirk
Error - ***remove all fellows
ORA-08601: SQL assertion (PRACTICALPLSQL.INTERNS_REQUIRE_FELLOWS) violated.
We can also refactor this assertion to use existential syntax to check that “no departments which have interns do not have fellows“.
create.assertion.interns_require_fellows.sql
drop assertion if exists interns_require_fellows;
create assertion if not exists interns_require_fellows check (
not exists (
select 'a department'
from univ_depts d
where
exists (
select 'has interns'
from univ_dept_roles i
where
i.dept_name = d.dept_name
and i.role_name = 'intern'
and not exists (
select 'has research fellow'
from univ_dept_roles f
where
f.dept_name = i.dept_name
and f.role_name = 'fellow'))
)
)
/
Running the test script we see that the assertion is working in the refactored existential form.
@test.assertion.interns_require_fellows.sql
Success - ***remove all fellows and interns before testing
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
secretary: Kirk
Staff before ***add interns without any fellows
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
secretary: Kirk
Error - ***add interns without any fellows
ORA-08601: SQL assertion (PRACTICALPLSQL.INTERNS_REQUIRE_FELLOWS) violated.
Success - ***add interns and fellows
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
fellow: Joyce, Picasso
intern: James, Jones, Newton
secretary: Kirk
Staff before ***remove all fellows
Philosophy staff members:
admin: Pike
chair: Descartes, Wittgenstein
faculty: Descartes, Moore, Pascal, Picasso, Russell, Wittgenstein
fellow: Joyce, Picasso
intern: James, Jones, Newton
secretary: Kirk
Error - ***remove all fellows
ORA-08601: SQL assertion (PRACTICALPLSQL.INTERNS_REQUIRE_FELLOWS) violated.
Because this assertion only involves evaluations between role assignments to the same department, we can also simplify it to only use the univ_dept_roles table. This approach would simply check that “there does not exist an intern where there does not exist a fellow in the same department“.
create.assertion.interns_require_fellows.alt.sql
drop assertion if exists interns_require_fellows;
create assertion if not exists interns_require_fellows check (
not exists (
select 'an intern'
from univ_dept_roles i
where
i.role_name = 'intern'
and not exists (
select 'research fellow in same department'
from univ_dept_roles f
where
f.dept_name = i.dept_name
and f.role_name = 'fellow')
)
)
/
Running the test script again we can see that all tests have passed, our department interns will always have supervision because of the assertion.
Testing: The Math Department
What happens when multiple assertions are violated at the same time? Which assertion will actually raise an exception? To test this we can try creating a Math department with an invalid composition: 3 chairs, 2 admins, an intern and no faculty. This will violate four different assertions: faculty is required, only one admin, limit two chairs and no unsupervised interns.
insert.math_department.sql
declare
l_action varchar2(100) := '***create math department with 3 chairs, 2 admins, an intern and no faculty';
d univ_depts.dept_name%type := 'Math';
c univ_roles.role_name%type := 'chair';
a univ_roles.role_name%type := 'admin';
begin
insert into univ_depts
set dept_name = d;
insert into univ_dept_roles(dept_name, role_name, staff_name)
values
(d, c, 'Pascal'), (d, c, 'Russell'), (d, c, 'Newton')
, (d, 'intern', 'Newton')
, (d, a, 'Moore'), (d, a, 'James');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
exception
when others then
rollback;
dbms_output.put_line('Error - ' || l_action);
dbms_output.put_line(dbms_utility.format_error_stack());
end;
/
Error - ***create math department with 3 chairs, 2 admins, an intern and no faculty
ORA-08601: SQL assertion (PRACTICALPLSQL.INTERNS_REQUIRE_FELLOWS) violated.
The test failed on the unsupervised interns assertion. The two deferred assertions would not have failed until commit. There is no real way to know which of the other assertions will fail first. Unlike triggers, there are no FOLLOWS/PRECEDES options to designate the order in which assertions will be validated.
If we satisfy all of the rules in the transaction, we can successfully create the Math department:
declare
l_action varchar2(100) := '***create math department with valid composition';
d univ_depts.dept_name%type := 'Math';
f univ_roles.role_name%type := 'faculty';
c univ_roles.role_name%type := 'chair';
begin
insert into univ_depts
set dept_name = d;
insert into univ_dept_roles(dept_name, role_name, staff_name)
values
(d, c, 'Pascal'), (d, c, 'Russell')
, (d, f, 'Pascal'), (d, f, 'Newton')
, (d, 'fellow', 'Moore'), (d, 'intern', 'James')
, (d, 'admin', 'Picasso');
commit;
dbms_output.put_line('Success - ' || l_action);
display_staff(d);
end;
/
Success - ***create math department with valid composition
Math staff members:
admin: Picasso
chair: Pascal, Russell
faculty: Newton, Pascal
fellow: Moore
intern: James
We were able to create a valid Math department where all of the roles added up! Using assertions instead of writing validation code for checking the department composition allows the business rules to be enforced even when we just write to the table with sql and don’t precheck what we are inserting.
Conclusion
Assertions are well suited for implementing rules governing group composition. Looking at a variety of assertions in action, we can see that the sql used for the assertions can be subject to any number of refactorings for clarity and simplicity. Instead of just using a verbose explicit expression of a business rule, more compact expressions of the same logic are achievable.
Because assertions can create dependencies between rows in multiple tables, it may be necessary to create some assertions as deferrable in order to satisfy these dependencies.
We have seen that the existential assertion syntax lends itself easily to testing the sql syntax and correctness. The universal syntax requires some modifications in order to test.
We have created a set of assertions to check the business rules as given for our use case. It took quite a few assertions to get there, but any transaction or statement that violates a business rule will fail and the data in the system will be well validated.
The assertion names reflect the rules which each assertion enforces. This results in a very specific error message that can be easily interpreted to see what rule has been violated. A more generic approach would lack this advantage.
The drawback to this approach is that the role names are all mentioned explicitly in the assertions, except for the ‘No More Than Two Roles In A Department’ rule. If role names are changed over time, the assertions will need to be rewritten.
Overall, we have created assertions that validate the following patterns of group composition using sql that explicitly mentions the roles for each rule and assertion names that reflect the assertion contents:
- One Required Role Type Assigned: At Least One X. At least one faculty member per department.
- One Required Role Type Is Assigned With Alternatives: At Least One X or Y. At least one admin or secretary per department.
- Limit of One Role of a Type: At Most One X. At most one admin per department.
- Limit of Two Role Assignments: At Most Two X. At most two chairs. At most two roles per staff member.
- Related: If X Is Present, Y Is Required. If departments have interns, they must have research fellows.
WIth this approach, if we want to implement new rules we will have to write new assertions. To enforce a limit of two research fellows and a maximum of one secretary, we would need two more assertions. These new assertions would be exactly like other existing assertions with the name of the role being the only difference.
Because these patterns look more general than our business rules, it may be possible to use a more generalized approach to create assertions that are capable of enforcing multiple similar rules.
In the next part of this series I will investigate ways to make these same rules more data driven. This is done in the hopes that the rules can be less bound to the role names and that the assertions can adapt dynamically to new roles that follow established patterns of group composition.
Anthony Harper
Leave a Reply