Updatable vs. Read-Only Views

Learning Focus

Use this lesson to understand when views allow data modifications and when they don't — a critical distinction that affects how you design data access layers.

Concept Overview

Can You Write Through a View?

Some MySQL views let you run INSERT, UPDATE, and DELETE against them, and those changes flow through to the underlying base table. These are called updatable views.

Other views are read-only — you can SELECT from them, but any attempt to modify data will fail with an error.

The difference depends entirely on how the view's SELECT query is structured.

Why Does This Matter?

• Updatable views are useful for creating a simplified write interface (hide certain columns while still allowing inserts/updates)
• Read-only views are natural for reporting, dashboards, and analytics — they shape data for reading only
• Assuming all views are writable is a common bug — your application code tries to UPDATE a view that can't be updated and crashes

The Core Rule

A view is updatable if MySQL can trace every row in the view back to exactly one row in exactly one base table.

If the view uses JOINs, aggregation, DISTINCT, subqueries, or UNION — MySQL cannot trace rows back to the base table, so the view is read-only.

---

Basic Syntax & Rules

Updatable View — What Makes It Qualify

A view is updatable if its SELECT query does NOT contain:

Feature	Examples	Makes View Read-Only?
Aggregate functions	SUM(), COUNT(), AVG(), MAX()	✅ Yes — read-only
GROUP BY	GROUP BY customer_id	✅ Yes — read-only
DISTINCT	SELECT DISTINCT ...	✅ Yes — read-only
UNION / UNION ALL	Combined result sets	✅ Yes — read-only
Subqueries in SELECT list	SELECT (SELECT ...)	✅ Yes — read-only
JOINs (in most cases)	FROM a JOIN b ON ...	✅ Usually — read-only
HAVING	HAVING COUNT(*) > 5	✅ Yes — read-only
Derived tables	FROM (SELECT ...)	✅ Yes — read-only
Simple WHERE filter on one table	WHERE status = 'active'	❌ No — still updatable

Checking If a View Is Updatable

SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

TABLE_NAME	IS_UPDATABLE
v_active_customers	YES
rpt_monthly_revenue	NO

---

Step-by-Step Examples

Example 1: Updatable View — Filtered Access

Step 1: Create a simple filtered view

CREATE VIEW v_active_customers AS
SELECT customer_id, full_name, email, phone, status
FROM customers
WHERE status = 'active';

Step 2: Verify it's updatable

SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'v_active_customers';
-- Result: IS_UPDATABLE = YES

Step 3: Insert through the view

INSERT INTO v_active_customers (full_name, email, phone, status)
VALUES ('Diana Lee', 'diana@example.com', '+62-812-3456', 'active');

This inserts directly into the customers table.

Step 4: Update through the view

UPDATE v_active_customers
SET phone = '+62-812-9999'
WHERE customer_id = 5;

Step 5: Delete through the view

DELETE FROM v_active_customers WHERE customer_id = 5;

All three operations work because the view is a simple filter on one table.

---

Example 2: Read-Only View — Aggregation

Step 1: Create a view with aggregation

CREATE VIEW rpt_monthly_revenue AS
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

Step 2: Verify it's read-only

SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'rpt_monthly_revenue';
-- Result: IS_UPDATABLE = NO

Step 3: Try to update (fails)

UPDATE rpt_monthly_revenue SET revenue = 0 WHERE month = '2026-01';
-- ERROR 1288: The target table rpt_monthly_revenue of the UPDATE
-- is not updatable

This makes sense — you can't "update a SUM" because SUM doesn't correspond to any single row.

---

Example 3: The WITH CHECK OPTION

When you have an updatable view with a WHERE clause, you might accidentally insert or update a row so that it falls outside the view's filter:

-- View shows only active customers
CREATE VIEW v_active_customers AS
SELECT customer_id, full_name, email, status
FROM customers
WHERE status = 'active';

-- This insert succeeds, but the new row won't be visible in the view!
INSERT INTO v_active_customers (full_name, email, status)
VALUES ('Ghost User', 'ghost@example.com', 'suspended');

The row is inserted into customers with status = 'suspended', but since the view filters on status = 'active', you'll never see it through the view. This is confusing.

Fix: Use WITH CHECK OPTION

CREATE OR REPLACE VIEW v_active_customers AS
SELECT customer_id, full_name, email, status
FROM customers
WHERE status = 'active'
WITH CHECK OPTION;

Now:

INSERT INTO v_active_customers (full_name, email, status)
VALUES ('Ghost User', 'ghost@example.com', 'suspended');
-- ERROR 1369: CHECK OPTION failed 'app_db.v_active_customers'

WITH CHECK OPTION prevents modifications that would make the row disappear from the view.

---

Example 4: JOINed View — Read-Only

CREATE VIEW v_order_details AS
SELECT
    o.order_id,
    c.full_name AS customer_name,
    o.total_amount,
    o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'v_order_details';
-- Result: NO

JOINed views are read-only because MySQL can't determine which base table should receive the modification.

---

Practical Use Cases

1. Simplified Write Interface

Create a view that hides auto-generated columns (id, created_at) and let the app INSERT through the view — cleaner interface.

2. Data Entry Forms

An updatable view showing only the columns a data entry operator needs to fill in, hiding internal fields.

3. Read-Only Dashboards

Aggregation views for dashboards that should never allow writes — naturally read-only by design.

4. Tenant-Scoped Writes

An updatable view filtered by tenant_id ensures users can only modify their own tenant's data.

5. Preventing Invisible Rows

WITH CHECK OPTION prevents inserting rows that would immediately fall outside the view's filter criteria.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Assuming all views are writable	Application errors on INSERT/UPDATE	Check IS_UPDATABLE in INFORMATION_SCHEMA.VIEWS
Inserting rows that disappear from the view	Row exists in base table but isn't visible through view	Use WITH CHECK OPTION
Trying to UPDATE a JOINed view	Error: target table is not updatable	Write the UPDATE directly against the base table
Using SELECT * in an updatable view	Adding columns to the base table silently changes the view	Explicitly list columns in the view
Not testing write operations against views	Code works against table, fails against view in production	Test INSERT/UPDATE/DELETE on the view during development

---

Best Practices

• Check updatability during development — query INFORMATION_SCHEMA.VIEWS before writing application code
• Use WITH CHECK OPTION on filtered updatable views — prevents invisible row insertion
• Keep updatable views simple — one table, no aggregation, no JOINs
• Document whether a view is updatable — annotate in your schema docs or naming convention
• Use read-only views for reporting — aggregation views are naturally non-updatable, which is correct behavior
• Prefer direct table access for writes — views are best for reads; use stored procedures or direct SQL for writes

---

Hands-On Practice

Exercise 1: Test Updatability (Easy)

Create the following two views and check which is updatable:

-- View A
CREATE VIEW v_cheap_products AS
SELECT product_id, name, price FROM products WHERE price < 50;

-- View B
CREATE VIEW v_category_summary AS
SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id;

Try running INSERT, UPDATE, and DELETE on each. What happens?

Exercise 2: WITH CHECK OPTION (Medium)

Create an updatable view v_premium_products showing products where price >= 100. Add WITH CHECK OPTION.

Then try to:

1. Insert a product with price = 200 (should succeed)
2. Insert a product with price = 50 (should fail)
3. Update a product to set price = 30 (should fail)

Exercise 3: Design Decision (Advanced)

Your team wants a view that shows each customer's total spending:

SELECT customer_id, full_name, SUM(total_amount) AS lifetime_value
FROM customers JOIN orders ON ...
GROUP BY customer_id;

1. Is this view updatable? Why or why not?
2. If the team wants to "update" a customer's name through this view, what's the correct approach?
3. Propose an alternative design that keeps the reporting view and also allows name updates.

---

Connection to Other Concepts

Related Concept	How It Connects
Creating Views	This lesson builds on view creation fundamentals
User Management	Grant SELECT-only on read-only views, SELECT+INSERT+UPDATE on updatable views
Stored Procedures	Use procedures for complex write operations instead of updatable views
Triggers	INSTEAD OF triggers don't exist in MySQL, so you can't make read-only views "appear" updatable
CHECK Constraints	WITH CHECK OPTION on views is conceptually similar to CHECK constraints on tables

What to Learn Next

• 13. Stored Procedures and Functions — for complex write logic that views can't handle

---

Visual Learning Diagram

flowchart TD
    A["View Query Structure"] --> B{"Contains aggregation,\nGROUP BY, DISTINCT,\nJOIN, UNION?"}
    B -->|No| C["✅ Updatable View"]
    B -->|Yes| D["❌ Read-Only View"]
    C --> E{"Has WHERE filter?"}
    E -->|Yes| F["Add WITH CHECK OPTION\nto prevent invisible rows"]
    E -->|No| G["Directly updatable"]
    D --> H["SELECT only — no writes allowed"]

    classDef updatable fill:#28a745,stroke:#fff,color:#fff
    classDef readonly fill:#dc3545,stroke:#fff,color:#fff
    class C,G,F updatable
    class D,H readonly

Quick Reference

-- Check if a view is updatable
SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- Updatable view with check option
CREATE VIEW v_active AS
SELECT id, name, status FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- Write through an updatable view
INSERT INTO v_active (name, status) VALUES ('New User', 'active');
UPDATE v_active SET name = 'Updated' WHERE id = 1;
DELETE FROM v_active WHERE id = 1;

What's Next

• 13. Stored Procedures and Functions