Updatable views in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an updatable view in MySQL?

An updatable view is a virtual table that allows you to perform INSERT, UPDATE, or DELETE operations on it, and these changes affect the underlying base tables.

intermediate

Which conditions must a view meet to be updatable in MySQL?

The view must select from a single table, not use GROUP BY, DISTINCT, or aggregate functions, and include all NOT NULL columns without default values from the base table.

intermediate

Can you update a view that uses JOINs in MySQL?

No, views that use JOINs are generally not updatable because MySQL cannot determine how to apply changes to multiple base tables.

beginner

What happens if you try to update a non-updatable view in MySQL?

MySQL will return an error saying the view is not updatable and will not allow the operation.

advanced

How can you make a complex view updatable in MySQL?

You can create INSTEAD OF triggers to define how INSERT, UPDATE, or DELETE operations on the view affect the base tables, but MySQL does not support INSTEAD OF triggers, so you must use simpler views or handle updates in application logic.

Which of the following views is updatable in MySQL?

A A view selecting from a single table without GROUP BY or DISTINCT

B A view using GROUP BY to aggregate data

C A view joining two tables

D A view using DISTINCT keyword

Only views selecting from a single table without GROUP BY or DISTINCT are updatable in MySQL.

What happens if you try to update a view that uses JOINs in MySQL?

A The update succeeds and changes all joined tables

B The update is ignored silently

C The update only affects the first table

D MySQL returns an error saying the view is not updatable

MySQL does not allow updates on views with JOINs and returns an error.

Which SQL operation can you perform on an updatable view?

A UPDATE

B All of the above

C DELETE

D INSERT

Updatable views allow INSERT, UPDATE, and DELETE operations that affect the base table.

Why might a view not be updatable in MySQL?

A It selects from a single table

B It includes all NOT NULL columns

C It uses aggregate functions like SUM or COUNT

D It has no WHERE clause

Views using aggregate functions are not updatable because the data is summarized.

Does MySQL support INSTEAD OF triggers to make complex views updatable?

A No, MySQL does not support INSTEAD OF triggers

B Yes, fully supported

C Only for DELETE operations

D Only for INSERT operations

MySQL does not support INSTEAD OF triggers, so complex views cannot be made updatable this way.

Explain what an updatable view is and list the main conditions that make a view updatable in MySQL.

Think about how changes in the view affect the base table.

Describe why views with JOINs are not updatable in MySQL and what alternatives exist to handle updates on complex views.

Consider how MySQL manages data changes behind the scenes.