Oracle SQL Experts: Oracle WITH CHECK OPTION View FAQs with Examples

The WITH CHECK OPTION clause is used when creating a view to ensure that rows modified through the view continue to satisfy the view's WHERE condition.

It is especially useful when you want users to insert or update rows only within the subset of data exposed by the view.

1. What is WITH CHECK OPTION in Oracle?

WITH CHECK OPTION tells Oracle:

Any INSERT or UPDATE performed through this view must result in a row that still satisfies the view's WHERE condition.

Example

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

The view shows only:

DEPARTMENT_ID = 10

Therefore, this is allowed:

UPDATE dept10_employees
SET salary = salary + 1000
WHERE employee_id = 101;

But this is rejected:

UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;

Why?

Because after the update, the employee would no longer satisfy:

department_id = 10

2. Why do we use WITH CHECK OPTION?

It prevents users from modifying data through a view in a way that makes the modified row disappear from the view.

Without WITH CHECK OPTION:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10;

A user might execute:

UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;

The update can move the employee out of department 10.

Afterward:

SELECT *
FROM dept10_employees;

will no longer show employee 101.

With WITH CHECK OPTION, Oracle prevents that type of update.

3. What is the basic syntax?

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1,
       column2,
       ...
FROM table_name
WHERE condition
WITH CHECK OPTION;

Example:

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE salary >= 50000
WITH CHECK OPTION;

The view allows only rows satisfying:

salary >= 50000

4. What happens without WITH CHECK OPTION?

Consider:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10;

Suppose:

EMPLOYEE_ID  FIRST_NAME  SALARY  DEPARTMENT_ID

101          JOHN        60000   10

Now:

UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;

The update can succeed if the view is otherwise updatable.

But now:

SELECT *
FROM dept10_employees;

doesn't show employee 101.

The row has moved outside the view's condition.

5. What happens with WITH CHECK OPTION?

Create:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Then:

UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;

Oracle rejects the operation because the resulting row would violate the view condition.

You may encounter an error such as:

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

The important concept is:

View condition
      ↓
UPDATE through view
      ↓
Does resulting row satisfy condition?
      ↓
YES → allowed
NO  → rejected

6. Does WITH CHECK OPTION apply to SELECT?

No.

It affects DML performed through an updatable view, particularly:

INSERT
UPDATE

It does not change the behavior of:

SELECT *
FROM dept10_employees;

The SELECT simply returns rows satisfying the view definition.

7. Does WITH CHECK OPTION affect DELETE?

No.

DELETE removes a row rather than modifying it into a state that violates the view's condition.

For example:

DELETE FROM dept10_employees
WHERE employee_id = 101;

The row is deleted from the underlying table if the view is otherwise deletable.

WITH CHECK OPTION is primarily concerned with ensuring that inserted or updated rows remain visible through the view.

8. Can we use WITH CHECK OPTION with INSERT?

Yes, provided the view is otherwise insertable.

Example:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

This is allowed:

INSERT INTO dept10_employees
(
    employee_id,
    first_name,
    salary,
    department_id
)
VALUES
(
    201,
    'DAVID',
    60000,
    10
);

Because:

department_id = 10
satisfies the view condition.

But:

INSERT INTO dept10_employees
(
    employee_id,
    first_name,
    salary,
    department_id
)
VALUES
(
    202,
    'ROBERT',
    60000,
    20
);

is rejected.

Why?

View condition:
department_id = 10

Inserted value:
department_id = 20

Result:
Condition violated

9. Can WITH CHECK OPTION be used with a salary condition?

Yes.

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE salary >= 50000
WITH CHECK OPTION;

This is allowed:

UPDATE high_salary_employees
SET salary = 60000
WHERE employee_id = 101;

But:

UPDATE high_salary_employees
SET salary = 40000
WHERE employee_id = 101;

is rejected because:

40000 < 50000

The resulting row would no longer satisfy the view condition.

10. Can WITH CHECK OPTION have a name?

Yes.

You can specify a constraint name.

Syntax:

WITH CHECK OPTION CONSTRAINT constraint_name

Example:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION CONSTRAINT dept10_check;

Here:

dept10_check

is the name of the CHECK OPTION constraint.

This can make error messages and metadata easier to understand.

11. What is WITH CASCADED CHECK OPTION?

CASCADED means the check is applied to the current view and relevant underlying views in the view hierarchy.

Example:

First view:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Now create another view on top of it:

CREATE OR REPLACE VIEW high_paid_dept10 AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM dept10_employees
WHERE salary >= 50000
WITH CASCADED CHECK OPTION;

There are now two conditions:

EMPLOYEES
    ↓
DEPT10_EMPLOYEES
department_id = 10
    ↓
HIGH_PAID_DEPT10
salary >= 50000

A modification through HIGH_PAID_DEPT10 must respect the applicable conditions.

12. What is WITH LOCAL CHECK OPTION?

LOCAL means the check is applied to the current view's condition, rather than cascading through all underlying view conditions in the same way as CASCADED.

Example:

CREATE OR REPLACE VIEW high_paid_dept10 AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM dept10_employees
WHERE salary >= 50000
WITH LOCAL CHECK OPTION;

The key interview distinction is:

LOCAL
↓
Checks the current view condition

CASCADED
↓
Checks the current view and applicable
underlying view conditions

13. What is the difference between LOCAL and CASCADED?

Consider:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Then:

CREATE OR REPLACE VIEW high_paid_dept10 AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM dept10_employees
WHERE salary >= 50000
WITH LOCAL CHECK OPTION;

Conceptually:

EMPLOYEES
   |
   | department_id = 10
   ↓
DEPT10_EMPLOYEES
   |
   | salary >= 50000
   ↓
HIGH_PAID_DEPT10

The distinction is:

Option	Meaning
LOCAL	Checks the current view's condition
CASCADED	Checks the current view and underlying view conditions
WITH CHECK OPTION	Oracle's default is effectively CASCADED

For interviews, remember:

LOCAL     → current view
CASCADED  → current + underlying views

14. What is the default if we simply write WITH CHECK OPTION?

If neither LOCAL nor CASCADED is specified:

WITH CHECK OPTION

Oracle treats it as:

WITH CASCADED CHECK OPTION

Conceptually:

WITH CHECK OPTION
is equivalent to:
WITH CASCADED CHECK OPTION

for this purpose.

15. Can WITH CHECK OPTION make a non-updatable view updatable?

No.

This is an important interview trap.

Suppose:

CREATE OR REPLACE VIEW dept_summary AS
SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
WITH CHECK OPTION;

Adding:

WITH CHECK OPTION

does not make this aggregate view updatable.

WITH CHECK OPTION controls which modifications are permitted through an already-updatable view.

It does not turn a non-updatable view into an updatable one.

16. Can WITH CHECK OPTION be used with a simple view?

Yes. This is one of the most common uses.

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

This is a straightforward, updatable view with a restriction.

17. Can WITH CHECK OPTION be used with a complex view?

It can be used where the view definition and DML rules permit it, but WITH CHECK OPTION does not overcome the normal restrictions on complex views.

For example, an aggregate view:

CREATE OR REPLACE VIEW dept_summary AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
WITH CHECK OPTION;

is still not normally updatable.

Remember:

WITH CHECK OPTION
       ↓
Restricts DML through a view

NOT

WITH CHECK OPTION
       ↓
Makes every view updatable

18. Does WITH CHECK OPTION check the original or new values?

For an UPDATE, it effectively checks the resulting row against the view's condition.

Example:

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE salary >= 50000
WITH CHECK OPTION;

Suppose:

OLD salary = 60000

Then:

UPDATE high_salary_employees
SET salary = 45000
WHERE employee_id = 101;

The resulting salary is:

which violates:

salary >= 50000

Therefore the update is rejected.

19. What happens if an UPDATE doesn't affect the view condition?

Suppose:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

This is perfectly valid:

UPDATE dept10_employees
SET salary = salary + 5000
WHERE employee_id = 101;

The department remains:

Therefore:

department_id = 10
        ↓
condition satisfied
        ↓
UPDATE allowed

20. Can WITH CHECK OPTION protect multiple conditions?

Yes.

Example:

CREATE OR REPLACE VIEW dept10_high_salary AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
AND salary >= 50000
WITH CHECK OPTION;

Now the resulting row must satisfy:

department_id = 10
AND
salary >= 50000

This is allowed:

UPDATE dept10_high_salary
SET salary = 60000
WHERE employee_id = 101;

But this isn't:

UPDATE dept10_high_salary
SET salary = 30000
WHERE employee_id = 101;

because:

salary >= 50000
is false.

Likewise:

UPDATE dept10_high_salary
SET department_id = 20
WHERE employee_id = 101;

violates:

department_id = 10

21. Can WITH CHECK OPTION be used with a date condition?

Yes.

Example:

CREATE OR REPLACE VIEW recent_employees AS
SELECT employee_id,
       first_name,
       hire_date
FROM employees
WHERE hire_date >= DATE '2026-01-01'
WITH CHECK OPTION;

An update that changes hire_date to:

DATE '2025-01-01'

would violate the view condition and therefore be rejected.

22. Can WITH CHECK OPTION be used for security?

Yes, but it should be viewed primarily as a DML consistency mechanism, not as a complete security feature.

For example:

CREATE OR REPLACE VIEW department_10_data AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

This ensures that DML through the view cannot move a row outside department 10.

You can additionally control access:

GRANT SELECT, INSERT, UPDATE
ON department_10_data
TO app_user;

For robust security, Oracle's broader security features may also be appropriate depending on the requirement.

23. Does WITH CHECK OPTION prevent direct table updates?

No.

This is very important.

Suppose:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

The following through the view is restricted:

UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;

But if a user has direct privileges on EMPLOYEES, they could potentially execute:

UPDATE employees
SET department_id = 20
WHERE employee_id = 101;

WITH CHECK OPTION applies to DML through the view. It doesn't impose a database-wide constraint on the underlying table.

24. Is WITH CHECK OPTION a table constraint?

No.

It is a view clause.

Compare:

CHECK constraint
↓
Table-level data integrity

versus:

WITH CHECK OPTION
↓
Controls DML through a view

Example table constraint:

CREATE TABLE employees (
    employee_id NUMBER,
    salary      NUMBER
        CONSTRAINT emp_salary_ck CHECK (salary >= 0)
);

Example view check option:

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id,
       salary
FROM employees
WHERE salary >= 50000
WITH CHECK OPTION;

They solve different problems.

25. Can WITH CHECK OPTION replace a CHECK constraint?

Generally, no.

Suppose the business rule is:

Salary must never be negative.

That should normally be enforced with a table constraint:

CONSTRAINT emp_salary_ck CHECK (salary >= 0)

A view's WITH CHECK OPTION only controls modifications made through that particular view.

A user with another access path to the table could bypass the view.

26. Does WITH CHECK OPTION work with joins?

It can be used with views that are otherwise subject to Oracle's view-updatability rules, but joins can introduce additional restrictions.

Example:

CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id,
       e.first_name,
       e.salary,
       e.department_id,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 10
WITH CHECK OPTION;

The WITH CHECK OPTION reinforces the view condition:

e.department_id = 10

But it does not make every column in this join view automatically updatable.

27. What is a common real-world use case?

Suppose an application should manage only employees belonging to department 10.

Create:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

The application works with:

SELECT *
FROM dept10_employees;

and can perform valid updates:

UPDATE dept10_employees
SET salary = salary + 1000
WHERE employee_id = 101;

But it cannot use the view to move an employee to another department:

UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;

This makes WITH CHECK OPTION useful for restricted-subset views.

28. What is the difference between WITH CHECK OPTION and WITH READ ONLY?

This is a very common interview question.

WITH READ ONLY

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE department_id = 10
WITH READ ONLY;

No DML through the view:

SELECT → ✅
INSERT → ❌
UPDATE → ❌
DELETE → ❌

WITH CHECK OPTION

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

DML may be allowed, but resulting rows must continue to satisfy the view condition.

SELECT → ✅
INSERT → condition must remain true
UPDATE → condition must remain true
DELETE → generally allowed if view is otherwise deletable

Easy way to remember:

READ ONLY
   ↓
No DML

CHECK OPTION
   ↓
DML allowed
   ↓
But don't violate the view condition

29. What happens if a row is inserted through a view but the WHERE condition isn't satisfied?

Example:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Attempt:

INSERT INTO dept10_employees
VALUES (301, 'MIKE', 60000, 20);

Oracle rejects it because:

Inserted department_id = 20

View requires:
department_id = 10

The new row would not belong to the view.

30. What is the most important interview scenario?

Consider:

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Then:

UPDATE emp_view
SET department_id = 20
WHERE employee_id = 101;

Interview answer

The update is rejected because WITH CHECK OPTION requires the resulting row to continue satisfying:

department_id = 10

Changing it to:

department_id = 20

violates the view's WHERE condition.

31. What are the common WITH CHECK OPTION interview traps?

Trap 1

Does WITH CHECK OPTION make a view updatable?

❌ No.

It only restricts DML through a view that is otherwise updatable.

Trap 2

Does it apply to SELECT?

❌ No.

It controls DML through the view.

Trap 3

Does it prevent direct updates to the base table?

❌ No.

It applies to DML performed through the view.

Trap 4

Does it prevent DELETE?

Generally ❌ no.

Its key purpose is to prevent INSERT/UPDATE from producing rows outside the view's condition.

Trap 5

Can it be used with aggregate views to make them updatable?

❌ No.

An aggregate view remains subject to normal view-updatability restrictions.

Trap 6

What happens if an UPDATE causes a row to leave the view?

❌ The update is rejected.

Trap 7

What is the difference between LOCAL and CASCADED?

LOCAL
↓
Current view condition

CASCADED
↓
Current + underlying view conditions

32. WITH CHECK OPTION Cheat Sheet

VIEW
 |
 ↓
WHERE condition
 |
 ↓
WITH CHECK OPTION
 |
 +--------+--------+
 |                 |
INSERT            UPDATE
 |                 |
 +--------+--------+
          |
          ↓
Does resulting row
satisfy view condition?
          |
     +----+----+
     |         |
    YES        NO
     |         |
  Allowed    Rejected

Key points to remember

Feature	WITH CHECK OPTION
Used with views	✅
Controls DML through view	✅
Mainly important for INSERT/UPDATE	✅
Ensures row satisfies view condition	✅
Makes non-updatable view updatable	❌
Prevents direct base-table DML	❌
Same as table CHECK constraint	❌
WITH LOCAL CHECK OPTION	Current view condition
WITH CASCADED CHECK OPTION	Current + underlying conditions
Default when neither specified	CASCADED

Most important statement

WITH CHECK OPTION ensures that rows inserted or updated through a view continue to satisfy the view's WHERE condition.

And remember the distinction:

WITH READ ONLY
      ↓
DML NOT allowed

WITH CHECK OPTION
      ↓
DML may be allowed
      ↓
But resulting row must satisfy
the view's WHERE condition

Oracle SQL Experts

Home

Oracle WITH CHECK OPTION View FAQs with Examples

1. What is WITH CHECK OPTION in Oracle?

2. Why do we use WITH CHECK OPTION?

3. What is the basic syntax?

4. What happens without WITH CHECK OPTION?

5. What happens with WITH CHECK OPTION?

6. Does WITH CHECK OPTION apply to SELECT?

7. Does WITH CHECK OPTION affect DELETE?

8. Can we use WITH CHECK OPTION with INSERT?

9. Can WITH CHECK OPTION be used with a salary condition?

10. Can WITH CHECK OPTION have a name?

11. What is WITH CASCADED CHECK OPTION?

12. What is WITH LOCAL CHECK OPTION?

13. What is the difference between LOCAL and CASCADED?

14. What is the default if we simply write WITH CHECK OPTION?

15. Can WITH CHECK OPTION make a non-updatable view updatable?

16. Can WITH CHECK OPTION be used with a simple view?

17. Can WITH CHECK OPTION be used with a complex view?

18. Does WITH CHECK OPTION check the original or new values?

19. What happens if an UPDATE doesn't affect the view condition?

20. Can WITH CHECK OPTION protect multiple conditions?

21. Can WITH CHECK OPTION be used with a date condition?

22. Can WITH CHECK OPTION be used for security?

23. Does WITH CHECK OPTION prevent direct table updates?

24. Is WITH CHECK OPTION a table constraint?

25. Can WITH CHECK OPTION replace a CHECK constraint?

26. Does WITH CHECK OPTION work with joins?

27. What is a common real-world use case?

28. What is the difference between WITH CHECK OPTION and WITH READ ONLY?

29. What happens if a row is inserted through a view but the WHERE condition isn't satisfied?

30. What is the most important interview scenario?

31. What are the common WITH CHECK OPTION interview traps?

32. WITH CHECK OPTION Cheat Sheet

Most important statement

No comments:

Post a Comment