DROP VIEW to Remove Views

1. Introduction

Similar to the other objects that we have been able to create, we can use the DROP VIEW statement to remove a view from the database. The syntax of the DROP VIEW command looks like the following:

DROP VIEW <viewname>;

2. Dropping a View

For simplicity, we would just add the view’s name after the DROP VIEW keywords. If we had a view created for the album and artist names:

CREATE VIEW album_artist_names
AS
SELECT album.title, artist.name
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id;

If we wanted to drop the view, it would look like this:

DROP VIEW album_artist_names;

We were successful in dropping the view. However, it is possible that a view does not exist as it may be labeled incorrectly or has been dropped already. There are some additional options that can be used in conjunction with the statement as parameters. One of those includes the IF EXISTS statement to avoid having an error message being presented if the view does not exist.

For example, if we tried to drop the view a second time:

DROP VIEW album_artist_names;

However, by using the IF EXISTS parameter, the database will only drop the view if it exists, if not, no error messages will be generated:

DROP VIEW IF EXISTS album_artist_names;

3. View Dependencies

There are also other instances in which you may have other objects like another view that uses a view that you have created. The RESTRICT parameter can be added although it is used by default to block the view from being dropped. Say we had the album_artist_names view created and then another view called temp_album_artist_names created that queried from the album_artist_names:

CREATE VIEW album_artist_names
AS
SELECT album.title, artist.name
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id;

CREATE VIEW temp_album_artist_names
AS
SELECT *
FROM album_artist_names;

If we tried to drop the album_artist_names view, we will get an error as the temp_album_artist_names table uses that view:

DROP VIEW IF EXISTS album_artist_names
RESTRICT;

We can use the CASCADE option, in this case, to have the statement automatically drop all of the objects that depend on the view and all objects that also then depend on those other objects. The statement with the cascade would look like this:

DROP VIEW IF EXISTS album_artist_names
CASCADE;

You do have to be careful of such a statement as you could have a lot of unintended objects getting dropped. Instead, you can drop them in order:

DROP VIEW IF EXISTS temp_album_artist_names;

DROP VIEW IF EXISTS album_artist_names;

You can also drop multiple views at the same time as an alternative if you do have dependencies:

DROP VIEW IF EXISTS album_artist_names, temp_album_artist_names;

Notice that even though we do have a dependency between the two views, dropping them both on the same line will have the database handle that for us simplifying that process.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.