Update Records via View with Enforced Conditions

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

Update Data through a View with WITH CHECK OPTION

Write a PostgreSQL query to update an employee's department via a view that enforces a condition using WITH CHECK OPTION.

Solution:

-- Update the department of an active employee via the ActiveEmployees view.
UPDATE ActiveEmployees  -- The view includes only rows where status = 'active'.
SET department = 'Marketing'  -- Set the new department.
WHERE employee_id = 102;  -- Identify the employee.

Explanation:

Purpose of the Query:

The goal is to update data via a view that restricts modifications to rows meeting a specific condition.
This demonstrates the use of WITH CHECK OPTION, which ensures that any change still satisfies the view’s condition.

Key Components:

UPDATE ActiveEmployees : Specifies the view that filters active employees.
SET department = 'Marketing' : Updates the department column.
WHERE employee_id = 102 : Targets a particular row.

Real-World Application:

Helps enforce business rules, such as only updating records for active employees.

Notes:

If the update would cause a row to violate the view’s condition, the operation is rejected.

For more Practice: Solve these Related Problems:

Write a PostgreSQL query to update the salary via a view that only permits changes if the new salary exceeds the department’s average, enforced by WITH CHECK OPTION.
Write a PostgreSQL query to update an employee’s role through a view that restricts updates to active employees using WITH CHECK OPTION.
Write a PostgreSQL query to update the department field in a view that validates the new department ID against a list of allowed IDs via WITH CHECK OPTION.
Write a PostgreSQL query to update an employee’s contact information through a view that enforces a regex pattern on the phone number using WITH CHECK OPTION.

Go to:

PREV : Update Data through a Simple Updatable View.
NEXT : Update Data through a View Using an INSTEAD OF Trigger.

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