Views

Views are "virtual" relations (as opposed to tables that are "physical" relations) that are not stored. Some views are updatable when the DBMS can translate the modification of the view into an equivalent modification on the base table. In this exercise, we will explore the difference between updatable views and non-updatable views.

Setup

Login to your MySQL account and use a database that allows you to create tables:

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(50) UNIQUE
);

CREATE TABLE employees (
  emp_id   INT PRIMARY KEY AUTO_INCREMENT,
  emp_name VARCHAR(50),
  salary   DECIMAL(10,2),
  dept_id  INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INSERT INTO departments (dept_name) VALUES ('HR'), ('IT'), ('Finance');

INSERT INTO employees (emp_name, salary, dept_id) VALUES
('Alice', 60000, 1),
('Bob',   80000, 2),
('Carol', 90000, 2),
('Dan',   75000, 3);

A) Create an Updatable View

A simple projection and selection over one table should be updatable because each row in the view corresponds to a single row in the base table.

CREATE VIEW it_employees AS
SELECT emp_name, salary
FROM employees
WHERE dept_id = 2;

Run show tables; , do you see it_employees?

Try describe it_emplyees;, what do you see? What about show create view it_employees;?

Give Bob a $5000 raise by updating the view. What do you see? Is the operation successful? (scroll down to see the answer)

Try to add a new IT employee through the view with (emp_id=5, emp_name='Eve', salary=85000). What do you see? Is the operation successful? (scroll down to see the answer)

B) Create a Non-Updatable View

CREATE VIEW emp_dept_view AS
SELECT e.emp_name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Try to set Alice's salary to $70000 through the view. What do you get? (scroll down to see the answer)

Try to update Alice's department to 'IT". What do you get? (scroll down to see the answer)

Try to update fields from two base tables:

UPDATE emp_dept_view
SET salary = salary + 1000,           -- employees column
    dept_name = CONCAT(dept_name,'*') -- departments column
WHERE emp_name = 'Alice';

What do you get? (scroll down to see the answer)

C) Create a view with GROUP BY

DROP VIEW IF EXISTS avg_salary_by_dept;
CREATE VIEW avg_salary_by_dept AS
SELECT d.dept_name, AVG(e.salary) AS avg_sal
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

Try to update through this view:

UPDATE avg_salary_by_dept
SET avg_sal = 100000
WHERE dept_name = 'IT';

What do you get? (scroll down to see the answer)

Solution queries

UPDATE it_employees
SET salary = salary + 5000
WHERE emp_name = 'Bob';

INSERT INTO it_employees (emp_id, emp_name, salary)
VALUES (5, 'Eve', 85000);

MySQL updates the underlying employees table automatically because this view meets the requirements for an updatable view (single base table, no GROUP BY, DISTINCT, aggregate, or join).

UPDATE emp_dept_view
SET salary = 70000
WHERE emp_name = 'Alice';

MySQL updates the underlying employees table automatically because this view meets the requirements for an updatable view (single base table, no GROUP BY, DISTINCT, aggregate, or join).

UPDATE emp_dept_view
SET dept_name = 'IT'
WHERE emp_name = 'Alice';

This should cause ERROR 1062 (23000): Duplicate entry 'IT' for key 'dept_name'.

UPDATE emp_dept_view
SET salary = salary + 1000,           -- employees column
    dept_name = CONCAT(dept_name,'*') -- departments column
WHERE emp_name = 'Alice';

This should cause ERROR 1393 (HY000): Can not modify more than one base table through a join view 'test.emp_dept_view'.

UPDATE avg_salary_by_dept
SET avg_sal = 100000
WHERE dept_name = 'IT';

MySQL should reject the update because the view contains GROUP BY/aggregate, which the manual lists as non-updatable constructs. Any of the listed constructs (GROUP BY, DISTINCT, aggregates, UNION, etc.) makes a view non-updatable. (MySQL Developer Zone)