Oracle WITH CHECK OPTION

Summary: in this tutorial, you will learn to use the Oracle WITH CHECK OPTION clause to ensure the view’s consistency.

Oracle WITH CHECK OPTION clause

The WITH CHECK OPTION clause is used for an updatable view to prohibit the changes to the view that would produce rows that are not included in the defining query.

The following statement creates a view that has rows meet the condition of the WHERE clause.

CREATE
    VIEW view_name AS SELECT
        *
    FROM
        table_name
    WHERE
        condition;
Code language: SQL (Structured Query Language) (sql)

In case the view is updatable, you can update the rows to make them invisible in the view or you can insert new rows which make the condition in the WHERE the clause is not true.

To prevent the invisible rows from being updated to non-visible rows, you use the WITH CHECK OPTION clause:

CREATE
    VIEW view_name AS SELECT
        *
    FROM
        table_name
    WHERE
        condition WITH CHECK OPTION;
Code language: SQL (Structured Query Language) (sql)

Oracle WITH CHECK OPTION example

We will use the brands and cars tables created in the updatable view tutorial for the demonstration.

The following pictures illustrate the data in the brands and cars tables:

SELECT
	*
FROM
	brands;Code language: SQL (Structured Query Language) (sql)

SELECT
	*
FROM
	cars;Code language: SQL (Structured Query Language) (sql)

The following statement creates audi_cars view that returns only Audi cars:

CREATE
    VIEW audi_cars AS SELECT
        car_id,
        car_name,
        brand_id
    FROM
        cars
    WHERE
        brand_id = 1;
Code language: SQL (Structured Query Language) (sql)

The following query returns the data from the audi_cars view:

SELECT
    *
FROM
    audi_cars;
Code language: SQL (Structured Query Language) (sql)

The audi_cars is an updatable view so you can insert a new row into the cars table through it:

INSERT
    INTO
        audi_cars(
            car_name,
            brand_id
        )
    VALUES(
        'BMW Z3 coupe',
        2
    ); 
Code language: SQL (Structured Query Language) (sql)

The statement inserted a row which makes the condition in the WHERE clause ( brand_id = 1) not true.

You can also make the visible rows in the view invisible by updating the data in the underlying base table through the view as shown in the following example:

UPDATE
    audi_cars
SET
    car_name = 'BMW 1-serie Coupe',
    brand_id = 2
WHERE
    car_id = 3;
Code language: SQL (Structured Query Language) (sql)

This statement changed both the brand and name of a car with id 3 from Audi to BMW that makes the row invisible in the view.

SELECT
    *
FROM
    audi_cars;
Code language: SQL (Structured Query Language) (sql)

Let’s create another updatable view named ford_cars that has the WITH CHECK OPTION clause:

CREATE
    VIEW ford_cars AS SELECT
        car_id,
        car_name,
        brand_id
    FROM
        cars
    WHERE
        brand_id = 3 WITH CHECK OPTION;
Code language: SQL (Structured Query Language) (sql)

The following statement inserts an Audi car into the cars table through the ford_cars view:

INSERT
    INTO
        ford_cars(
            car_name,
            brand_id
        )
    VALUES(
        'Audi RS6 Avant',
        1
    );
Code language: SQL (Structured Query Language) (sql)

Unlike the audi_cars view, Oracle issued the following error in this case:

SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violationCode language: SQL (Structured Query Language) (sql)

Because the INSERT statement attempted to insert a row that causes a view WITH CHECK OPTION where-clause violation.

Similarly, the following UPDATE statement also fails to update because of the where-clause violation.

UPDATE
    ford_cars
SET
    brand_id = 4,
    car_name = 'Honda NSX'
WHERE
    car_id = 6;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle WITH CHECK OPTION clause in the CREATE VIEW statement to ensure the view’s consistency.