Vote for “Create assertion” to be added
Posted by John Hallas on May 31, 2016
There is a standard for SQL code known as ANSI-92 which is adopted by many of the main RDBMS vendors. However each vendor has its own differences from the standard.
Oracle are currently taking votes on a proposal to add the CREATE ASSERTION statement to Oracle SQL on the OTN community page https://community.oracle.com/ideas/13028
Here are two examples of how the statement could be used
This SQL statement creates an assertion to demand that there’s no more than a single president among the employees:
create assertion AT_MOST_ONE_PRESIDENT as CHECK ((select count(*) from EMP e where e.JOB = 'PRESIDENT') <= 1 )
This SQL statement creates an assertion to demand that Boston based departments do not employ trainers:
create assertion NO_TRAINERS_IN_BOSTON as CHECK (not exists (select 'trainer in Boston' from EMP e, DEPT d where e.DEPTNO = d.DEPTNO and e.JOB = 'TRAINER' and d.LOC = 'BOSTON') )
To implement the same functionality now you would use a combination of check constraints and/or triggers.
I have cast my vote in favour