Part 112: Updatable Views & The WITH CHECK OPTION

Under certain conditions, you can execute INSERT, UPDATE, and DELETE through a view.

1. The WITH CHECK OPTION

Prevents updates that would cause a row to disappear from the view:

CREATE VIEW california_customers AS
SELECT id, name, state FROM customers WHERE state = 'CA'
WITH CHECK OPTION;

-- ❌ FAILS! Throws an error because state is 'NY', violating the view definition:
UPDATE california_customers SET state = 'NY' WHERE id = 1;

Key Takeaways

• WITH CHECK OPTION prevents clients from inserting or updating rows that don't satisfy the view's filter.

In Part 113, we explore Materialized Views: Caching Expensive Analytical Queries!