Using the WITH CHECK OPTION clause

ASA SQL User's Guide
Working with Database Objects
Working with views

Using the WITH CHECK OPTION clause

Even when INSERT and UPDATE statements are allowed against a view, it is possible that the inserted or updated rows in the underlying tables may not meet the requirements for the view itself. For example, the view has no new rows even though the INSERT or UPDATE modified the underlying tables.

Examples using the WITH CHECK OPTION clause

The following example illustrates the usefulness of the WITH CHECK OPTION clause. This optional clause is the final clause in the CREATE VIEW statement.

To create a view displaying the employees in the sales department (SQL)

Type the following statements:

CREATE VIEW sales_employee
AS SELECT emp_id,
      emp_fname,
      emp_lname,
      dept_id
FROM employee
WHERE dept_id = 200

The contents of this view are as follows:

SELECT *
FROM sales_employee

They appear in Interactive SQL as follows:

emp_id	emp_fname	emp_lname	dept_id
129	Philip	Chin	200
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
...	...	...	...

Transfer Philip Chin to the marketing department This view update causes the entry to vanish from the view, as it no longer meets the view selection criterion.
```
UPDATE sales_employee
SET dept_id = 400
WHERE emp_id = 129
```

List all employees in the sales department Inspect the view.

SELECT *
FROM sales_employee

Emp_id	Emp_fname	Emp_lname	Dept_id
129	Philip	Chin	200
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
...	...	...	...

List all employees in the sales department Inspect the view.

SELECT *
FROM sales_employee

Emp_id	emp_fname	emp_lname	dept_id
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
641	Thomas	Powell	200
...	...	...	...

When you create a view using the WITH CHECK OPTION, any UPDATE or INSERT statement on the view is checked to ensure that the new row matches the view condition. If it does not, the operation causes an error and is rejected.

The following modified sales_employee view rejects the update statement, generating the following error message:

Invalid value for column 'dept_id' in table 'employee'

Create a view displaying the employees in the sales department (second attempt) Use WITH CHECK OPTION this time.

CREATE VIEW sales_employee
AS SELECT emp_id, emp_fname, emp_lname, dept_id
    FROM employee
    WHERE dept_id = 200
WITH CHECK OPTION

The check option is inherited

If a view (say V2) is defined on the sales_employee view, any updates or inserts on V2 that cause the WITH CHECK OPTION criterion on sales_employee to fail are rejected, even if V2 is defined without a check option.