Practical 9: Views

Selecting some columns only

CREATE VIEW member_public AS
SELECT member_id, member_name, course FROM member;

SELECT * FROM member_public ORDER BY member_id LIMIT 3;

+-----------+---------------+--------+
| member_id | member_name   | course |
+-----------+---------------+--------+
|         1 | Asha Kulkarni | BSc IT |
|         2 | Ravi Deshmukh | BSc IT |
|         3 | Meena Iyer    | BSc CS |
+-----------+---------------+--------+

city and joined_on are not in the view, so a user given access to member_public and nothing else cannot see them at all. This is column level security, done with a view.

An updatable view

Some views can be written to, and the change goes through to the table underneath.

CREATE VIEW cheap_books AS
SELECT book_id, title, subject, price FROM book WHERE price < 500;

SELECT book_id, title, price FROM cheap_books ORDER BY book_id;

+---------+-----------------+--------+
| book_id | title           | price  |
+---------+-----------------+--------+
|       1 | The C Language  | 395.00 |
|       3 | MySQL Reference | 499.00 |
|       6 | Learn SQL       | 280.00 |
|       8 | Discrete Maths  | 430.00 |
|      10 | New Arrival     | 350.00 |
+---------+-----------------+--------+

UPDATE cheap_books SET price = 310.00 WHERE book_id = 6;

SELECT book_id, title, price FROM book WHERE book_id = 6;

+---------+-----------+--------+
| book_id | title     | price  |
+---------+-----------+--------+
|       6 | Learn SQL | 310.00 |
+---------+-----------+--------+

The update went through the view into book.

A view is updatable only when there is a clear one to one correspondence between its rows and the rows of a table. A view is not updatable if it contains a DISTINCT, a GROUP BY or HAVING, an aggregate function, a UNION, or a subquery in the select list.

CREATE VIEW books_per_subject AS
SELECT subject, COUNT(*) AS books FROM book GROUP BY subject;

UPDATE books_per_subject SET books = 99 WHERE subject = 'Databases';

ERROR 1288 (HY000): The target table books_per_subject of the UPDATE is not updatable

Error 1288. There is no row of book that the number 3 belongs to; it was computed from three of them, and there is no way to push a change to it back.

A join view, and the surprise in it

A view over a join is updatable, provided the change touches only one of the joined tables. That is more permissive than most students expect, and it is worth seeing:

UPDATE book_with_author SET author_name = 'D. M. Ritchie' WHERE book_id = 1;

SELECT author_id, author_name FROM author WHERE author_id = 1;

+-----------+---------------+
| author_id | author_name   |
+-----------+---------------+
|         1 | D. M. Ritchie |
+-----------+---------------+

The statement named a book and changed an author. author_name belongs to author, so the update went there, and the name is now different for every book Dennis Ritchie wrote, not just for book 1.

That is correct behaviour and it is a trap worth knowing about: an update through a join view changes the table the column came from, not the rows the view showed. If that is not what you want, update the base table directly, where the statement says plainly what it is changing.

UPDATE author SET author_name = 'Dennis Ritchie' WHERE author_id = 1;

WITH CHECK OPTION, which is what MU names

Here is the problem the clause solves. The view shows books under 500. Nothing so far stops you putting a book through the view that does not belong in it:

INSERT INTO cheap_books VALUES (11, 'Expensive Book', 'Databases', 900.00);

SELECT book_id, title, price FROM book WHERE book_id = 11;

+---------+----------------+--------+
| book_id | title          | price  |
+---------+----------------+--------+
|      11 | Expensive Book | 900.00 |
+---------+----------------+--------+

SELECT book_id, title FROM cheap_books WHERE book_id = 11;

The row went into book and then vanished from the view, because 900 is not under 500. A user working only through cheap_books has inserted a row and cannot see it afterwards. That is the anomaly, and it is confusing enough to be worth a clause of its own.

CREATE VIEW cheap_books_checked AS
SELECT book_id, title, subject, price FROM book WHERE price < 500
WITH CHECK OPTION;

INSERT INTO cheap_books_checked VALUES (12, 'Another Costly One', 'Databases', 950.00);

ERROR 1369 (HY000): CHECK OPTION failed 'librarydb.cheap_books_checked'

Error 1369. WITH CHECK OPTION refuses any insert or update through the view that would produce a row the view cannot see. A row that does belong still goes in:

INSERT INTO cheap_books_checked VALUES (13, 'Pocket Guide', 'Programming', 120.00);

SELECT book_id, title, price FROM cheap_books_checked WHERE book_id = 13;

+---------+--------------+--------+
| book_id | title        | price  |
+---------+--------------+--------+
|      13 | Pocket Guide | 120.00 |
+---------+--------------+--------+

There are two forms of the clause, and they differ only when views are built on views:

CASCADED is the default when neither word is given, which is what the view above used.

Listing and dropping

SELECT table_name AS views_here
FROM information_schema.views
WHERE table_schema = DATABASE()
ORDER BY table_name;

+---------------------+
| views_here          |
+---------------------+
| book_with_author    |
| books_per_subject   |
| cheap_books         |
| cheap_books_checked |
| member_public       |
+---------------------+

DROP VIEW cheap_books_checked;

SELECT table_name AS views_left
FROM information_schema.views
WHERE table_schema = DATABASE()
ORDER BY table_name;

+-------------------+
| views_left        |
+-------------------+
| book_with_author  |
| books_per_subject |
| cheap_books       |
| member_public     |
+-------------------+

Dropping a view destroys no data. The view was only a stored query; the rows were always in the tables and are still there.

SELECT COUNT(*) AS books_still_here FROM book;

+------------------+
| books_still_here |
+------------------+
|               11 |
+------------------+

DROP VIEW IF EXISTS v; does not complain when the view is not there. CREATE OR REPLACE VIEW v AS ... redefines an existing view in one statement, which is how a view is changed.

Dropping a table that a view is built on is allowed, and the view then breaks the next time somebody selects from it, which is a genuine trap on a shared database.

View against table

What beginners get wrong

Thinking a view stores a copy. It stores the query.

Expecting every view to be updatable. A view with GROUP BY, DISTINCT, an aggregate or a UNION is not.

Inserting through a view without WITH CHECK OPTION and then losing the row from sight.

Dropping a view and expecting the data to go. It does not; that is DROP TABLE.

Using DROP TABLE on a view. It is refused; a view is dropped with DROP VIEW.

Building a view on a view on a view. It works and it becomes very hard to see what will run.

Forgetting that the underlying table can be changed or dropped without the view being told.

Quick revision

• A view is a stored query with a name; it holds no data.
• CREATE VIEW v AS SELECT ...; and then select from v like a table.
• Uses: simplicity, security, a shared definition, independence from the tables.
• A view is always current, because the query runs each time.
• Updatable only with a one to one row correspondence: no DISTINCT, GROUP BY, aggregate or UNION.
• Without WITH CHECK OPTION, a row inserted through a view can fall outside it and vanish.
• WITH CHECK OPTION refuses such a row. Error 1369.
• LOCAL checks this view; CASCADED, the default, checks the views underneath too.
• DROP VIEW v; removes the definition and no data. CREATE OR REPLACE VIEW redefines one.

What goes in your journal

Aim, the CREATE VIEW, a SELECT from it, and then the pair that is the whole practical: an insert through a view without the check option, and the same insert through a view with it.

Write the two results out in full. The first shows the row in the table and absent from the view; the second shows error 1369. Those two together explain the clause in a way no sentence does, and the question "what is the use of WITH CHECK OPTION" is what a viva asks about views.

Test yourself

1. What is a view? A named stored query. It looks like a table and holds no data; the query runs each time the view is used.

2. Give two reasons for using one. It hides the complexity of a long query behind a name, and it lets a user be given access to some columns or rows without access to the whole table.

3. When is a view not updatable? When its rows do not correspond one to one with rows of a table: if it contains DISTINCT, GROUP BY, HAVING, an aggregate function, a UNION, or a subquery in the select list.

4. What does WITH CHECK OPTION do? It refuses any insert or update made through the view that would produce a row the view's own condition excludes.

5. What happens without it? The row goes into the table and then disappears from the view, so the user who inserted it cannot see it.

6. Does DROP VIEW delete data? No. Only the stored query is removed; the tables and their rows are untouched.

7. What is the difference between LOCAL and CASCADED? LOCAL checks only this view's condition. CASCADED, which is the default, also checks the conditions of any views this one is built on.