Insert with check option

One of the ways to validate data while insertion, directly at the database level is : INSERT …. WITH CHECK OPTION

Let’s take an example and see what it is.

Database version : Oracle 10g.

Schema : Scott

Let’s create a test table from the standard “emp” table and work on it.

create table test_emp as select * from emp;

Let us suppose that our requirement is something like this :

Hence forth, in Department 30 manager’s should have commission between 750 – 1000 (included).

For our requirement above, the select query would be :

select *
from test_emp e
where e.deptno = 30
and e.job = 'MANAGER'
and e.comm >= 750
and e.comm <= 1000;

Usually the developers take care of this validation on the front-end itself. If there is only one point of data entry to your database (Eg : Web front-end), it’s okay to validate at the entry point. But think of a situation where there are multiple point of data entries to your database (Eg : Web front-end, Feeds, Web Services, JMS, MDB’s …etc.,).

So,

a) You have to duplicate the validation (it is almost impossible to maintain a common code base between these many discrete systems. Even if you have a common code base, on system might just not invoke the validation at all.)

b) If a common code base for validations is not used, then there could be a bug or missing implementation in any of the systems.

So, how about having a validation just before inserting the data into the database ? PL/SQL strikes immediately right 

We can put the volition logic in a pl/sql procedure/function and let all the above discrete systems invoke it.

But…….. there is a performance overhead. We are adding another layer after data access layer. For simple/moderate systems, it is okay. What we have a high volume system.

Oracle “almost always” has an option.

Oracle prohibits any changes to the table or view that would produce the rows that are not included in the sub query.

Make the above select query as a sub query to insert() as shown below.

insert into
(select *
from test_emp e
where e.deptno = 30
and e.job = 'MANAGER'
and e.comm >= 750
and e.comm <= 1000) values (7935, 'New Guy', 'MANAGER', 7782, sysdate, 1600.00, 500, 30);

The data that we are trying to insert in this query is against our rules, as the commission is = 500.

If you execute the above insert, Oracle still inserts the record.

select * from test_emp e where e.empno = 7935;

1 7935 New Guy MANAGER 7782 5/29/2008 9:02:05 AM 1600.00 500.00 30

Provide “WITH CHECK OPTION” in the sub query as shown below :

insert into
(select *
from test_emp e
where e.deptno = 30
and e.job = 'MANAGER'
and e.comm >= 750
and e.comm <= 1000 with check option)
values
(7935, 'New Guy', 'MANAGER', 7782, sysdate, 1600.00, 500, 30);

When you try to execute the above insert, you will get an error message :

ORA-01402 : view WITH CHECK OPTION where-clause violation

So, no matter where the data is coming from, we could perform a validation directly on the insert itself.

Also, note that we are NOT performing an extra query, as the sub query is within the insert itself.

Useful links :

http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html
http://www.orafaq.com/node/55
http://www.psoug.org/reference/analytic_functions.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3170642805938

Hope this helps.

Long Live “Tom Kyte”.

Good Luck,
r-a-v-i