Oracle DBA – A lifelong learning experience

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: