Restrict Data Modifications with a View Using Check Option

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.

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

Previous PostgreSQL Exercise: Create a View with Column Aliases.
Next PostgreSQL Exercise: Rename a View.