EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQLite Drop Table
 

SQLite Drop Table

Updated June 8, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

SQLite Drop Table 1

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

SQLite Drop Table 2

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.

SQLite Drop Table 3

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.

SQLite Drop Table 4

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.

SQLite Drop Table 5

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

SQLite Drop Table 6

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.

ss

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW