How to Create View with CHECK OPTION in PostgreSQL

In PostgreSQL, you create a view with CHECK OPTION by adding WITH CHECK OPTION at the end of the CREATE VIEW statement. This ensures that any inserted or updated rows through the view meet the view's defining condition.

Syntax

The syntax to create a view with a check option in PostgreSQL is:

• CREATE VIEW view_name AS SELECT ...: Defines the view with a SELECT query.
• WITH CHECK OPTION: Ensures that any data inserted or updated through the view satisfies the view's WHERE condition.

sql

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition
WITH CHECK OPTION;

Example

This example creates a view showing only employees from the 'Sales' department and uses WITH CHECK OPTION to prevent inserting or updating employees outside 'Sales' through the view.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL
);

INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'HR'),
('Carol', 'Sales');

CREATE VIEW sales_employees AS
SELECT * FROM employees
WHERE department = 'Sales'
WITH CHECK OPTION;

-- This insert will succeed
INSERT INTO sales_employees (name, department) VALUES ('Dave', 'Sales');

-- This insert will fail because department is not 'Sales'
-- INSERT INTO sales_employees (name, department) VALUES ('Eve', 'HR');

-- This update will fail because it tries to change department to 'HR'
-- UPDATE sales_employees SET department = 'HR' WHERE name = 'Alice';

-- Query the view
SELECT * FROM sales_employees;

Output

 id | name  | department 
----+-------+------------
  1 | Alice | Sales     
  3 | Carol | Sales     
  4 | Dave  | Sales     
(3 rows)

Common Pitfalls

Common mistakes when using WITH CHECK OPTION include:

• Forgetting to add WITH CHECK OPTION, which allows inserts or updates that violate the view's condition.
• Trying to insert or update rows through the view that do not satisfy the view's WHERE clause, causing errors.
• Using complex views with joins where WITH CHECK OPTION may not behave as expected.

sql

/* Wrong: No CHECK OPTION, allows invalid data */
CREATE VIEW v_no_check AS
SELECT * FROM employees WHERE department = 'Sales';

INSERT INTO v_no_check (name, department) VALUES ('Eve', 'HR'); -- This succeeds but breaks the view logic

/* Right: With CHECK OPTION, prevents invalid data */
CREATE VIEW v_with_check AS
SELECT * FROM employees WHERE department = 'Sales'
WITH CHECK OPTION;

-- INSERT INTO v_with_check (name, department) VALUES ('Eve', 'HR'); -- This fails

Quick Reference

Clause	Description
CREATE VIEW view_name AS SELECT ...	Defines the view with a SELECT query.
WHERE condition	Filters rows shown in the view.
WITH CHECK OPTION	Prevents inserts/updates that violate the view's condition.

Key Takeaways

Use WITH CHECK OPTION to enforce data integrity when inserting or updating through a view.

WITH CHECK OPTION ensures all changes meet the view's WHERE condition.

Without WITH CHECK OPTION, invalid data can be inserted or updated through the view.

CHECK OPTION works best with simple views filtering on one table.

Attempting to insert or update rows violating the view condition will cause errors with CHECK OPTION.