Update Records via View with Enforced Conditions

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.

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

Previous PostgreSQL Exercise: Update Data through a Simple Updatable View.
Next PostgreSQL Exercise: Update Data through a View Using an INSTEAD OF Trigger.