Views with CHECK OPTION in PostgreSQL

Introduction

Views with CHECK OPTION make sure that any data added or changed through the view fits the rules of that view. This keeps the data clean and correct.

When you want to let users add or change data only if it meets certain conditions.

When you create a view that shows only part of a table and want to stop changes that don't fit that part.

When you want to protect data by making sure updates through a view don't break its rules.

When you want to simplify data entry but keep control over what data is allowed.

When you want to create a safe window into your data for specific users or tasks.

Syntax

PostgreSQL

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

The WITH CHECK OPTION ensures that any INSERT or UPDATE through the view must satisfy the view's WHERE condition.

If a change tries to break the condition, the database will reject it.

Examples

This view shows only customers with status 'active'. Any changes through this view must keep the status 'active'.

PostgreSQL

CREATE VIEW active_customers AS
SELECT * FROM customers
WHERE status = 'active'
WITH CHECK OPTION;

This view shows employees earning more than 50000. Updates or inserts must keep salary above 50000.

PostgreSQL

CREATE VIEW high_salary_employees AS
SELECT id, name, salary FROM employees
WHERE salary > 50000
WITH CHECK OPTION;

Sample Program

We create a table and a view for employees with salary above 50000. Inserting Charlie works because salary is 70000. Inserting Dave fails because salary is 40000, which breaks the view's condition.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employees (name, salary) VALUES
('Alice', 60000),
('Bob', 45000);

CREATE VIEW high_salary_employees AS
SELECT id, name, salary FROM employees
WHERE salary > 50000
WITH CHECK OPTION;

-- This insert will succeed
INSERT INTO high_salary_employees (name, salary) VALUES ('Charlie', 70000);

-- This insert will fail because salary is not > 50000
INSERT INTO high_salary_employees (name, salary) VALUES ('Dave', 40000);

Important Notes

WITH CHECK OPTION only affects data changes made through the view, not direct table changes.

If the view has multiple conditions, all must be met for changes to succeed.

This option helps keep data consistent and prevents accidental bad data entry.

Summary

Views with CHECK OPTION keep data changes inside the view's rules.

They stop inserts or updates that don't fit the view's filter.

This helps protect and keep data clean when using views.