EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development SQL Tutorials SQLite Drop Table
Secondary Sidebar
file_put_contents in PHP

is_array in PHP

Introduction to Python

Python Competitive Programming

Java NIO SocketChannel

Python Books

SQLite Drop Table

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,883 ratings)

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
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more