Introduction to the PostgreSQL WITH CHECK OPTION clause

In PostgreSQL, a view is a named query stored in the PostgreSQL database server. A simpleview can be updatable.

To ensure that any data modification made through a view adheres to certain conditions in the view’s definition, you use theWITH CHECK OPTION clause.

Typically, you specify theWITH CHECK OPTION when creating a view using theCREATE VIEW statement:

CREATE VIEW view_name ASqueryWITH CHECK OPTION;

When you create a viewWITH 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

TheLOCAL 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 theWITH LOCAL CHECK OPTION:

CREATE VIEW view_nameASqueryWITH LOCAL CHECK OPTION;

TheCASCADED scope extends the check option enforcement to all underlying views of the current view. Here’s the syntax for creating a view with theWITH CASCADED CHECK OPTION.

CREATE VIEW view_nameASqueryWITH CASCADED CHECK OPTION;

To change the scope of check for an existing view, you can use theALTER VIEW statement.

PostgreSQL WITH CHECK OPTION examples

Let’s take some examples of using theWITH CHECK OPTION.

Setting up a sample table

The following statementscreate a new table calledemployees andinsert data into it:

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_typeIN ('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 calledfte that retrieves the employees with the typeFTE from theemployees table:

CREATE OR REPLACE VIEW fte ASSELECT  id,  first_name,  last_name,  department_id,  employee_typeFROM  employeesWHERE  employee_type= 'FTE';

Second, retrieve data from thefte view:

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 theemployees table via thefte view:

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 ofContractor into theemployee table via the view that exposes the employee to the type ofFTE.

To ensure that we can insert only employees with the typeFTE into theemployees table via thefte view, you can use theWITH CHECK OPTION:

Fourth, replace thefte view and add theWITH CHECK OPTION:

CREATE OR REPLACE VIEW fte ASSELECT  id,  first_name,  last_name,  department_id,  employee_typeFROM  employeesWHERE  employee_type= 'FTE'WITH CHECK OPTION;

After adding theWITH CHECK OPTION, you perform insert,update, anddelete onemployees table that satisfies theWHERE clause in the defining query of the view.

For example, the followingINSERT statement will fail with an error:

INSERT INTO fte(first_name, last_name, department_id, employee_type)VALUES ('John','Snow',1,'Contractor');

Error:

ERROR:  newrow violatescheck option for view"fte"DETAIL:  Failingrow contains (12, John, Snow,1, Contractor).

The reason is that theemployee_typeContractor does not satisfy the condition defined in the defining query of the view:

employee_type= 'FTE';

But if you modify the row with the employee typeFTE, it’ll be fine.

Fifth, change the last name of the employee id2 to'Doe':

UPDATE fteSET last_name= 'Doe'WHERE id= 2;

It works as expected.

2) Using WITH LOCAL CHECK OPTION example

First, recreate thefte view without using theWITH CHECK OPTION:

CREATE OR REPLACE VIEW fte ASSELECT  id,  first_name,  last_name,  department_id,  employee_typeFROM  employeesWHERE  employee_type= 'FTE';

Second, create a new viewfte_1 based on thefte view that returns theemployees of department1, with theWITH LOCAL CHECK OPTION:

CREATE OR REPLACE VIEW fte_1ASSELECT  id,  first_name,  last_name,  department_id,  employee_typeFROM  fteWHERE  department_id= 1WITH LOCAL CHECK OPTION;

Third, retrieve the data from thefte_1 view:

SELECT * FROM fte_1;

Output:

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 theWITH LOCAL CHECK OPTION, PostgreSQL checks only thefte_1 view when we modify the data in theemployees table via thefte_1 view.

Fourth, insert a new row into theemployees table via thefte_1 view:

INSERT INTO fte_1(first_name, last_name, department_id, employee_type)VALUES ('Miller','Jackson', 1, 'Contractor');

It succeeded. The reason is that theINSERT statement inserts a row with department 1 that satisfies the condition in thefte_1 view:

department_id= 1

Fifth, query data from theemployees table:

SELECT  *FROM  employeesWHERE  first_name = 'Miller'  and last_name = 'Jackson';

Output:

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 viewfte_1 with theWITH CASCADED CHECK OPTION:

CREATE OR REPLACE VIEW fte_1ASSELECT  id,  first_name,  last_name,  department_id,  employee_typeFROM  fteWHERE  department_id = 1WITH CASCADED CHECK OPTION;

Second, insert a new row intoemployee table via thefte_1 view:

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).

TheWITH CASCADED CHECK OPTION instructs PostgreSQL to check the constraint on thefte_1 view and also its base view which is thefte view.

That’s why theINSERT statement fails the condition of bothfte_1 andfte views.

Summary

• Use theWITH CHECK OPTION clause to enforce constraints on data modifications through views and ensure that only valid data can be changed.

Last updated onMarch 16, 2024

On this page

• Introduction to the PostgreSQL WITH CHECK OPTION clause
• PostgreSQL WITH CHECK OPTION examples
• Summary