Updatable Views

In this lesson, you will learn how modifying data through a view affects the actual base tables and why not every view is "updatable."

The Bridge to Your Base Tables

Think of a view as a window looking into your database. When you perform an UPDATE, INSERT, or DELETE on a view, you aren't actually changing the view itself (since it’s just a stored query); you are pushing those changes through the window into the underlying base table.

If you have a view called active_employees defined as:

CREATE VIEW active_employees AS
SELECT employee_id, name, department_id
FROM employees
WHERE status = 'Active';

When you run UPDATE active_employees SET name = 'Jane Doe' WHERE employee_id = 101;, the database transparently performs this update on the employees table. This is incredibly useful for providing a simplified interface for users while keeping the data integrity of your main tables intact.

When Can You Update?

While this sounds convenient, there are strict rules. For a view to be "updatable," the database must be able to trace every row in the view back to a specific, unique row in a single base table.

If your view is too complex, the database can no longer guarantee where the data should go. A view is generally not updatable if it contains:

• Aggregates: Functions like SUM(), AVG(), or COUNT().
• Grouping: A GROUP BY clause.
• Set Operators: UNION, INTERSECT, or EXCEPT.
• Joins: In many systems, updating through a view that joins multiple tables is restricted because the database wouldn't know which table to update to satisfy your request.
• Calculated Columns: If a column is the result of a calculation (like price * 1.1), you cannot update it because it doesn't exist as a literal value in the base table.

The Benefit

The primary benefit is data abstraction. You can restrict a user to only see and modify a specific subset of columns or rows, preventing them from accidentally touching sensitive data in the base table that isn't part of the view.

Key Takeaway: Updatable views allow you to modify base table data through a simplified interface, but they are only possible when the view maps clearly and unambiguously to a single source table.