SQLite DROP VIEW

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a tiny, quick, self-contained, reliable, full-featured SQL database engine.

In this article, we will primarily focus on the DROP VIEW command of SQLite, along with its syntax, examples, and so on.

VIEWS in SQLite

Views are like virtual tables that consist of rows and columns, a view can consist of all the columns or some specifically mentioned columns based on mentioned conditions. Views mostly act as a Read Only version of the Table (if all the columns are being considered), so no unauthorized person can make any changes to the real table or manipulate it.

View also helps in simpler JOINS as it allows the users to choose columns from different tables simultaneously. One table can have an infinite number of views, so it becomes easy to understand the combination of some columns without fetching them from the original table.

Query:

Explanation: As we can see in the above output we can’t make any changes like INSERTING new rows to the View.

SQLite DROP VIEW Statement

SQLite offers a statement called DROP VIEW which is used to DROP/DELETE any view that exists in the database.

Syntax:

DROP VIEW <view_name>; 

Explanation of Syntax: As we can see in the syntax, we need to use the command DROP VIEW and then simply pass the view_name that we want to delete/drop.

There is another modified version of this command which checks whether the view exists or not before DROPPING it, this can be used when the developer is not sure whether the view exists or not. Using this version will also not return any error if the view doesn’t exist.

Syntax:

DROP VIEW IF EXISTS <view_name>;

Explanation of Syntax: Here, the condition IF EXISTS is attached with the DROP VIEW command, which ensures that DROP the view only if exists, otherwise don’t throw any error.

SQLite DROP VIEW Statement Examples

Example 1

In this example, We will use the simpler version of DROP VIEW to delete an already existing view. After dropping we will again run the same command to see what Error SQLite shows. To understand the DROP VIEW in understanding manner, We have a table called Employees on which we have already created view named v_salaries. If you don’t know How to Create a Table in SQLite and How to Create View in SQLite, then refer this.

We will DROP this v_salaries view using the DROP VIEW command.

Query:

DROP VIEW v_salaries;

Now, we will execute the tables dot-command to see the list of tables again and check whether the view exists or not.

Query:

.tables

Output:

Explanation: As we can see clearly, the view has been DROPPED.

Now, if we again try to run the simpler version of the DROP VIEW command we will see the below error:

Explanation: As we can see in the output, SQLite returned an error stating that the view doesn’t exists. Now we will see how to avoid this error using the modified version of the DROP VIEW with IF EXISTS condition.

Example 2

Here, We will use the modified version of the DROP VIEW statement with the IF EXISTS condition. This condition will help us avoid any error which was present in case of the previous example. Here we will again try to DROP the v_salaries VIEW which doesn’t exists anymore. But this time, as we are using IF EXISTS command, SQLite will not throw any error.

Query:

 DROP VIEW IF EXISTS v_salaries;

Output:

Explanation: As we can see in the output, no error has been thrown by SQLite even though the VIEW doesn’t exist. In this way, using the IF EXISTS attachment with the DROP VIEW command can be used to avoid any error, this is why it is always recommended to use this version until and unless the developer is sure that the VIEW exists and not been removed earlier.

Conclusion

In this article, We saw that how the DROP VIEW command of SQLite works and it’s modifications like the use of IF EXISTS statement attached with the DROP VIEW. We also saw how and when to use which version of the DROP VIEW command and also what are the benefits of each of the version. After reading whole article now we have clear understanding of DROP View and we can easily perform various operations.