|
 |
|
Oracle “with check option” syntax
Don Burleson
|
A “with check option” is designed for updatable views whereas a
“check constraint” (coinstraint_type “V”) specifies valid values for
an individual column:
region_name varchar(5),
CONSTRAINT check_region
CHECK (region_name in ’north’,’south’,’east’,’west’)
The “with check option”, it is used to prevent changes to a view
that would produce rows that are not included in a sub-query.
For example, here we create a view that will only allows updates to
dept=’finance’:
CREATE OR REPLACE VIEW
finance_dept
AS
SELECT name, salary FROM emp WHERE dept = ‘finance’
WITH CHECK OPTION;
This means only the record of employees in the finance department
can be updated using this finance_dept view. At update time we see:
insert into
(select * from emp where dept = ‘finance’ with check option)
values
(...);
update
(select
* from
(select sal, empno from emp, dept where dept = ‘finance’)
with
check option)
set sal =
100000 where ename = ‘KING’;
A violation produces:
ORA-01402: view WITH CHECK OPTION
where-clause violation
With an ORA-01412, the SQL tried to INSERT or UPDATE a record in a
view that contained a WITH CHECK OPTION. The resulting INSERT or
UPDATE violates the WHERE clause of the view.
Related links:
Oracle Update Statement
|
