Managing & Dropping Views0%

Managing & Dropping Views

Intermediate11 min read•Updated: 2026-09-12

Managing & Dropping Views

Database schemas evolve continuously. As business requirements change, tables gain or lose columns, business definitions shift, and views must be modified or retired.

In MySQL, managing views involves altering existing definitions using ALTER VIEW or CREATE OR REPLACE VIEW, inspecting view metadata, and safely removing obsolete views with DROP VIEW.

Modifying a View: ALTER VIEW vs CREATE OR REPLACE VIEW

MySQL supports two synonymous statements to modify an existing view:

1. ALTER VIEW Statement

1ALTER VIEW view_name AS
2SELECT column1, column2, ...
3FROM table_name
4WHERE condition;

Note: If view_name does not exist, ALTER VIEW throws an error.

2. CREATE OR REPLACE VIEW Statement

1CREATE OR REPLACE VIEW view_name AS
2SELECT column1, column2, ...
3FROM table_name
4WHERE condition;

Note: If view_name exists, it is replaced; if it does not exist, it is created cleanly. For this reason, CREATE OR REPLACE VIEW is preferred in idempotent deployment scripts.

Dropping Views with DROP VIEW

To permanently delete one or more views:

1DROP VIEW [IF EXISTS] view_name1 [, view_name2, ...];

Crucial Rule: Dropping a view never deletes the data in the underlying base tables! It only removes the stored query definition.

Impact of Base Table Alterations on Views

A view is dependent on the underlying columns referenced in its SELECT clause. Consider what happens when base tables change:

1CREATE TABLE products (
2    product_id INT PRIMARY KEY,
3    name VARCHAR(100),
4    cost_price DECIMAL(10, 2),
5    sale_price DECIMAL(10, 2)
6);
7 
8CREATE VIEW v_product_margins AS
9SELECT product_id, name, (sale_price - cost_price) AS margin
10FROM products;

If an administrator drops a column used by the view:

1ALTER TABLE products DROP COLUMN cost_price;

The view definition is not deleted, but querying it immediately yields:

1SELECT * FROM v_product_margins;
2-- ERROR 1356 (HY000): View 'store.v_product_margins' references invalid table(s) or column(s)

To resolve this, the view must be recreated with the updated schema.

View Execution Algorithms: MERGE vs TEMPTABLE

When creating or altering views, MySQL allows specifying the internal evaluation algorithm:

1CREATE ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} VIEW view_name AS ...

Algorithm	How it Works	Best For / Limitations
MERGE	The view's text is merged directly into the outer query before optimization. Filters in `WHERE` are pushed down.	Highly efficient, updatable views. Cannot be used with `GROUP BY`, `DISTINCT`, or aggregates.
TEMPTABLE	The view is materialized into a temporary table in memory/disk first; the outer query scans that temp table.	Required when views use `GROUP BY`, `HAVING`, or window functions. Not updatable.
UNDEFINED (Default)	MySQL automatically selects `MERGE` if possible, falling back to `TEMPTABLE`.	Recommended for general use.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What happens to base table rows when you execute DROP VIEW my_view;?

Question 2

What is the key operational difference between ALTER VIEW and CREATE OR REPLACE VIEW?

Question 3

What error occurs if a base table column referenced by an existing view is dropped?

Question 4

Which ALGORITHM option merges the view's query text directly into the outer caller query?

Question 5

How can you drop multiple views in a single SQL statement?

Next Lesson

Security & Data Abstraction with Views

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Updatable Views & WITH CHECK OPTION	Security & Data Abstraction with Views

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Security & Data Abstraction with Views

Managing & Dropping Views

Intermediate11 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Managing & Dropping Views

Database schemas evolve continuously. As business requirements change, tables gain or lose columns, business definitions shift, and views must be modified or retired.

In MySQL, managing views involves altering existing definitions using ALTER VIEW or CREATE OR REPLACE VIEW, inspecting view metadata, and safely removing obsolete views with DROP VIEW.

Modifying a View: ALTER VIEW vs CREATE OR REPLACE VIEW

MySQL supports two synonymous statements to modify an existing view:

1. ALTER VIEW Statement

1ALTER VIEW view_name AS
2SELECT column1, column2, ...
3FROM table_name
4WHERE condition;

Note: If view_name does not exist, ALTER VIEW throws an error.

2. CREATE OR REPLACE VIEW Statement

1CREATE OR REPLACE VIEW view_name AS
2SELECT column1, column2, ...
3FROM table_name
4WHERE condition;

Note: If view_name exists, it is replaced; if it does not exist, it is created cleanly. For this reason, CREATE OR REPLACE VIEW is preferred in idempotent deployment scripts.

Dropping Views with DROP VIEW

To permanently delete one or more views:

1DROP VIEW [IF EXISTS] view_name1 [, view_name2, ...];

Crucial Rule: Dropping a view never deletes the data in the underlying base tables! It only removes the stored query definition.

Impact of Base Table Alterations on Views

A view is dependent on the underlying columns referenced in its SELECT clause. Consider what happens when base tables change:

1CREATE TABLE products (
2    product_id INT PRIMARY KEY,
3    name VARCHAR(100),
4    cost_price DECIMAL(10, 2),
5    sale_price DECIMAL(10, 2)
6);
7 
8CREATE VIEW v_product_margins AS
9SELECT product_id, name, (sale_price - cost_price) AS margin
10FROM products;

If an administrator drops a column used by the view:

1ALTER TABLE products DROP COLUMN cost_price;

The view definition is not deleted, but querying it immediately yields:

1SELECT * FROM v_product_margins;
2-- ERROR 1356 (HY000): View 'store.v_product_margins' references invalid table(s) or column(s)

To resolve this, the view must be recreated with the updated schema.

View Execution Algorithms: MERGE vs TEMPTABLE

When creating or altering views, MySQL allows specifying the internal evaluation algorithm:

1CREATE ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} VIEW view_name AS ...

Algorithm	How it Works	Best For / Limitations
MERGE	The view's text is merged directly into the outer query before optimization. Filters in `WHERE` are pushed down.	Highly efficient, updatable views. Cannot be used with `GROUP BY`, `DISTINCT`, or aggregates.
TEMPTABLE	The view is materialized into a temporary table in memory/disk first; the outer query scans that temp table.	Required when views use `GROUP BY`, `HAVING`, or window functions. Not updatable.
UNDEFINED (Default)	MySQL automatically selects `MERGE` if possible, falling back to `TEMPTABLE`.	Recommended for general use.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What happens to base table rows when you execute DROP VIEW my_view;?

Question 2

What is the key operational difference between ALTER VIEW and CREATE OR REPLACE VIEW?

Question 3

What error occurs if a base table column referenced by an existing view is dropped?

Question 4

Which ALGORITHM option merges the view's query text directly into the outer caller query?

Question 5

How can you drop multiple views in a single SQL statement?

Next Lesson

Security & Data Abstraction with Views

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Updatable Views & WITH CHECK OPTION	Security & Data Abstraction with Views

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next