> This page location: PostgreSQL Views > WITH CHECK OPTION > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL WITH CHECK OPTION **Summary**: in this tutorial, you will learn how to create an updatable view using the `WITH CHECK OPTION` clause to ensure that the changes to the base tables through the view satisfy the view-defining condition. ## Introduction to the PostgreSQL WITH CHECK OPTION clause In PostgreSQL, a view is a named query stored in the PostgreSQL database server. A simple [view can be updatable](https://neon.com/postgresql/postgresql-views/postgresql-updatable-views). To ensure that any data modification made through a view adheres to certain conditions in the view's definition, you use the `WITH CHECK OPTION` clause. Typically, you specify the `WITH CHECK OPTION` when creating a view using the `CREATE VIEW` statement: ```sql CREATE VIEW view_name AS query WITH CHECK OPTION; ``` When you create a view `WITH CHECK OPTION`, PostgreSQL will ensure that you can only modify data of the view that satisfies the condition in the view's defining query (`query`). ### Scope of check In PostgreSQL, you can specify a scope of check: - `LOCAL` - `CASCADED` The `LOCAL` scope restricts the check option enforcement to the current view only. It does not enforce the check to the views that the current view is based on. Here's the syntax for creating a view with the `WITH LOCAL CHECK OPTION`: ```sql CREATE VIEW view_name AS query WITH LOCAL CHECK OPTION; ``` The `CASCADED` scope extends the check option enforcement to all underlying views of the current view. Here's the syntax for creating a view with the `WITH CASCADED CHECK OPTION`. ```sql CREATE VIEW view_name AS query WITH CASCADED CHECK OPTION; ``` To change the scope of check for an existing view, you can use the [ALTER VIEW](https://neon.com/postgresql/postgresql-views/postgresql-alter-view) statement. ## PostgreSQL WITH CHECK OPTION examples Let's take some examples of using the `WITH CHECK OPTION`. ### Setting up a sample table The following statements [create a new table](../postgresql-tutorial/postgresql-create-table) called `employees` and [insert data into it](../postgresql-tutorial/postgresql-insert-multiple-rows): ```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, department_id INT, employee_type VARCHAR(20) CHECK (employee_type IN ('FTE', 'Contractor')) ); INSERT INTO employees (first_name, last_name, department_id, employee_type) VALUES ('John', 'Doe', 1, 'FTE'), ('Jane', 'Smith', 2, 'FTE'), ('Bob', 'Johnson', 1, 'Contractor'), ('Alice', 'Williams', 3, 'FTE'), ('Charlie', 'Brown', 2, 'Contractor'), ('Eva', 'Jones', 1, 'FTE'), ('Frank', 'Miller', 3, 'FTE'), ('Grace', 'Davis', 2, 'Contractor'), ('Henry', 'Clark', 1, 'FTE'), ('Ivy', 'Moore', 3, 'Contractor'); ``` ### 1) Basic PostgreSQL WITH CHECK OPTION example First, create a view called `fte` that retrieves the employees with the type `FTE` from the `employees` table: ```sql CREATE OR REPLACE VIEW fte AS SELECT id, first_name, last_name, department_id, employee_type FROM employees WHERE employee_type = 'FTE'; ``` Second, retrieve data from the `fte` view: ```sql SELECT * FROM fte; ``` Output: ``` id | first_name | last_name | department_id | employee_type ----+------------+-----------+----------------+------------- 1 | John | Doe | 1 | FTE 2 | Jane | Smith | 2 | FTE 4 | Alice | Williams | 3 | FTE 6 | Eva | Jones | 1 | FTE 7 | Frank | Miller | 3 | FTE 9 | Henry | Clark | 1 | FTE (6 rows) ``` Third, insert a new row into the `employees` table via the `fte` view: ```sql INSERT INTO fte(first_name, last_name, department_id, employee_type) VALUES ('John', 'Smith', 1, 'Contractor'); ``` It succeeds. The issue is that we can insert an employee with the type of `Contractor` into the `employee` table via the view that exposes the employee to the type of `FTE`. To ensure that we can insert only employees with the type `FTE` into the `employees` table via the `fte` view, you can use the `WITH CHECK OPTION`: Fourth, replace the `fte` view and add the `WITH CHECK OPTION`: ```sql CREATE OR REPLACE VIEW fte AS SELECT id, first_name, last_name, department_id, employee_type FROM employees WHERE employee_type = 'FTE' WITH CHECK OPTION; ``` After adding the `WITH CHECK OPTION`, you perform insert, [update](../postgresql-tutorial/postgresql-update), and [delete](../postgresql-tutorial/postgresql-delete) on `employees` table that satisfies the `WHERE` clause in the defining query of the view. For example, the following `INSERT` statement will fail with an error: ```sql INSERT INTO fte(first_name, last_name, department_id, employee_type) VALUES ('John', 'Snow', 1, 'Contractor'); ``` Error: ``` ERROR: new row violates check option for view "fte" DETAIL: Failing row contains (12, John, Snow, 1, Contractor). ``` The reason is that the `employee_type` `Contractor` does not satisfy the condition defined in the defining query of the view: ```sql employee_type = 'FTE'; ``` But if you modify the row with the employee type `FTE`, it'll be fine. Fifth, change the last name of the employee id `2` to `'Doe'`: ```sql UPDATE fte SET last_name = 'Doe' WHERE id = 2; ``` It works as expected. ### 2) Using WITH LOCAL CHECK OPTION example First, recreate the `fte` view without using the `WITH CHECK OPTION`: ```sql CREATE OR REPLACE VIEW fte AS SELECT id, first_name, last_name, department_id, employee_type FROM employees WHERE employee_type = 'FTE'; ``` Second, create a new view `fte_1` based on the `fte` view that returns the `employees` of department `1`, with the `WITH LOCAL CHECK OPTION`: ```sql CREATE OR REPLACE VIEW fte_1 AS SELECT id, first_name, last_name, department_id, employee_type FROM fte WHERE department_id = 1 WITH LOCAL CHECK OPTION; ``` Third, retrieve the data from the `fte_1` view: ```sql SELECT * FROM fte_1; ``` Output: ```text id | first_name | last_name | department_id | employee_type ----+------------+-----------+---------------+--------------- 1 | John | Doe | 1 | FTE 6 | Eva | Jones | 1 | FTE 9 | Henry | Clark | 1 | FTE (3 rows) ``` Since we use the `WITH LOCAL CHECK OPTION`, PostgreSQL checks only the `fte_1` view when we modify the data in the `employees` table via the `fte_1` view. Fourth, insert a new row into the `employees` table via the `fte_1` view: ```sql INSERT INTO fte_1(first_name, last_name, department_id, employee_type) VALUES ('Miller', 'Jackson', 1, 'Contractor'); ``` It succeeded. The reason is that the `INSERT` statement inserts a row with department 1 that satisfies the condition in the `fte_1` view: ```sql department_id = 1 ``` Fifth, query data from the `employees` table: ```sql SELECT * FROM employees WHERE first_name = 'Miller' and last_name = 'Jackson'; ``` Output: ```text id | first_name | last_name | department_id | employee_type ----+------------+-----------+---------------+--------------- 12 | Miller | Jackson | 1 | Contractor (1 row) ``` ### 3) Using WITH CASCADED CHECK OPTION example First, recreate the view `fte_1` with the `WITH CASCADED CHECK OPTION`: ```sql CREATE OR REPLACE VIEW fte_1 AS SELECT id, first_name, last_name, department_id, employee_type FROM fte WHERE department_id = 1 WITH CASCADED CHECK OPTION; ``` Second, insert a new row into `employee` table via the `fte_1` view: ```sql INSERT INTO fte_1(first_name, last_name, department_id, employee_type) VALUES ('Peter', 'Taylor', 1, 'Contractor'); ``` Error: ``` ERROR: new row violates check option for view "fte" DETAIL: Failing row contains (24, Peter, Taylor, 1, Contractor). ``` The `WITH CASCADED CHECK OPTION` instructs PostgreSQL to check the constraint on the `fte_1` view and also its base view which is the `fte` view. That's why the `INSERT` statement fails the condition of both `fte_1` and `fte` views. ## Summary - Use the `WITH CHECK OPTION` clause to enforce constraints on data modifications through views and ensure that only valid data can be changed. --- ## Related docs (PostgreSQL Views) - [Create Views](https://neon.com/postgresql/postgresql-views/managing-postgresql-views) - [Drop Views](https://neon.com/postgresql/postgresql-views/postgresql-drop-view) - [Create Updatable Views](https://neon.com/postgresql/postgresql-views/postgresql-updatable-views) - [Materialized Views](https://neon.com/postgresql/postgresql-views/postgresql-materialized-views) - [Recursive View](https://neon.com/postgresql/postgresql-views/postgresql-recursive-view) - [Alter Views](https://neon.com/postgresql/postgresql-views/postgresql-alter-view) - [Listing Views](https://neon.com/postgresql/postgresql-views/postgresql-list-views)