SQLite Drop Table

Definition on SQLite Drop Table

SQLite provides the drop table functionality to the user. In which we are able to drop a single table at a time. If we required dropping multiple then we need to use multiple drop table statements. If we try to drop a non-existing table then it shows an error message. Basically, drop table command is used to drop the existing tables from the database as well as it also drops all dependent objects that are triggers and indexes of that specified table. Sometimes we need to drop the table from the specified table so that we can easily drop a table from the specified table by using the drop table command.

Syntax:

drop table [if exists] [specified schema name.] specified table name;

Explanation

In the above syntax we used the drop table command to drop the table, here if exists is used to check specified table name may available on a database or not, the specified schema name means our actual database name and the specified table name means an actual table that we need to drop from the database.

How to Drop Table in SQLite?

Now let’s see the how drop table works in SQLite as follows

The DROP TABLE statement eliminates a table added with the CREATE TABLE statement. The name determined is the table name. The dropped table is totally taken out from the data set blueprint and the disk document. The table cannot be recuperated. All files and triggers related to the table are additionally erased.

The discretionary IF EXISTS condition smothers the blunder that would ordinarily result if the table doesn’t exist. In the event that foreign key imperatives are empowered, a DROP TABLE command plays out an implied DELETE FROM command prior to eliminating the table from the information base schema. Any triggers appended to the table are dropped from the information base composition before the understood DELETE FROM is executed, so this can’t make any triggers fire. Conversely, a certain DELETE FROM makes any designed foreign key activities occur. In the event that the understood DELETE FROM executed as a component of a DROP TABLE order abuses any quick foreign key limitations, a blunder is returned and the table isn’t dropped. On the off chance that the understood DELETE FROM makes any conceded foreign key limitations be disregarded, and the infringement actually exists when the exchange is submitted, a blunder is returned at the hour of submitting.

Examples

Now let’s see the different examples of SQLite drop as follows.

First, we need to create two different tables by using the following statement as follows.

create table if not exists details (emp_address_id integer primary key, flat_no text, street text, emp_city text, pincode text, state text);

Explanation

In the above example, we use create table statement to create a new table name as details with a different attribute such as emp_address_id with integer data type and primary key constraint, flat_no with the text data type, a street with the text data type, emp_city with text data type and state with text data type as shown in the above statement. The end result of the above statement is shown in the below screenshot.

.table

Now create one more table that is emp by using the following statement as follows.

create table if not exists emp (emp_id integer primary key, emp_first_name text, emp_last_name text, emp_address_id integer, foreign key (emp_address_id) references details (emp_address_id));

Explanation

Similarly here we use create table statement to create a new table that is emp with different attributes such as emp_id with integer data type and primary key constraint, emp_first_name with the text data type, emp_last_name with the text data type, emp_address_id with integer and foreign key constraint as shown in above example. The end result of the above statement is shown in the below screenshot.

.table

Now insert some records into both tables by using the following statement as follows.

Insert into details (emp_address_id, flat_no, street, emp_city , pincode, state) values (123, “A-101”, “MG Road”, “Mumabi”, “451254”, “Maharashtra”);
select * from details;

Explanation

By using the above statement we inserted single records into the details table successfully. The end result of the above statement is shown in the below screenshot.

Similarly, insert records into the emp table as follows.

Insert into emp (emp_id, emp_first_name, emp_last_name, emp_address_id) values (44, “Sunny”, “Gupta”, 123);
select * from emp;

Explanation

By using the above statement we inserted single records into the emp table successfully. The end result of the above statement is shown in the below screenshot.

Now use the drop table command to drop the details table as follows.

drop table details;

Explanation

In the above example, we use the drop table command to drop the details table, but it shows an error message that is foreign key constraint failed. Because the drop command, violates the foreign key constraint. The end result of the above statement is shown in the below screenshot.

Suppose we need to drop a details table then we need to follow some steps as follows.

1. First we need to deactivate the foreign key constraints.

2. Then drop the details table.

3. After that we need to update the emp_address_id from the emp table.

4. Activate the foreign key constraints.

pragma foreign_keys = off;

After that drop, the details table as follows.

drop table details;

Explanation

Now see we successfully drop the table. The end result of the above statement is shown in the below screenshot.

.table

Now update the emp table by using the following statement as follows.

update emp set emp_address_id = null;

Now we successfully update the emp table and we set the emp_address_id as a null value.

Let’s see what happens when we try to drop the details table as follows.

drop table details;

Explanation

See here we try to drop a non-existent table that has details but it shows an error message. The end result of the above statement is shown below screenshot.

Conclusion

We hope from this article you have understood about the SQLite drop table. From the above article, we have learned the basic syntax of drop tables and we also see different examples of drop tables. From this article, we learned how and when we use the SQLite drop table.

Recommended Articles

This is a guide to SQLite Drop Table. Here we discuss the Introduction, syntax, How to Drop Table in SQLite? and examples. You may also have a look at the following articles to learn more –

1. SQL Injection Attack
2. SQL COALESCE
3. SQL Formatter
4. SQL Delete View