Restrict Data Modifications with a View Using Check Option

Last update on October 29 2025 12:36:57 (UTC/GMT +8 hours)

Create a View with WITH CHECK OPTION

Write a PostgreSQL query to create a view that enforces a condition on any data modifications performed through the view.

Solution:

-- Create a view that only allows operations on active employees.
CREATE VIEW ActiveEmployees AS
SELECT employee_id, name, department, status
FROM Employees
WHERE status = 'active'
WITH CHECK OPTION;

Explanation:

Purpose of the Query:

The goal is to ensure that any INSERT or UPDATE through the view adheres to the specified condition (active status).
This demonstrates the use of the WITH CHECK OPTION to enforce view constraints.

Key Components:

WHERE status = 'active' : Filters the data to include only active employees.
WITH CHECK OPTION : Prevents modifications that would violate the view’s condition.

Real-World Application:

Enhances data integrity by ensuring that changes made via the view remain consistent with its definition.

Notes:

The check option applies only to modifications made through the view, not direct changes to the base table.

For more Practice: Solve these Related Problems:

Write a PostgreSQL query to create a view that allows modifications only on records where the "status" is 'active' and "balance" is positive, using WITH CHECK OPTION.
Write a PostgreSQL query to create a view that enforces the condition that "start_date" is not in the past for any inserted or updated record, using WITH CHECK OPTION.
Write a PostgreSQL query to create a view that permits data changes only if the "email" column matches a valid email pattern, using WITH CHECK OPTION.
Write a PostgreSQL query to create a view that restricts modifications so that "quantity" does not exceed the available stock (using a subquery), with WITH CHECK OPTION.

Go to:

PREV : Create a View with Column Aliases.
NEXT : Rename a View.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.