SQLite - Deleting Database

In this article at OpenGenus, we explain how to delete data safely from SQLite tables using commands like DELETE and DROP TABLE, as well as foreign key constraints like ON DELETE. It emphasizes the importance of using these commands with caution to avoid unintentional data loss and maintain data integrity.

Table of Content :

1. Introduction
2. DELETE command
3. Foreign Key constraints
   • unsafe deletion
4. ON DELETE command
   • Explanation
   • schema.sql file
   • example using on DELETE function
5. conclusion.

---

Introduction

_
In addition to designing and writing data, it's important to be able to delete data from our tables as well. In situations such as a lost book or rented books that are being returned, we may need to delete data from our database. However, deleting data can be a scary task, as it's possible to accidentally delete the wrong data or mess up the data format. In the worst case scenario, we could delete the entire database without having a backup. To handle this task carefully, SQLite includes different commands and measures

In SQLite, there are several commands and measures that can help with deleting data safely. For example, the DELETE command can be used to delete specific rows from a table, while the DROP TABLE command can be used to delete an entire table. It's important to use these commands with caution and to double-check the syntax and parameters before executing them.

---

DELETE command

DELETE FROM "collections" WHERE "acquired" IS NULL;
SELECT * FROM "collections";

This SQLite code has two parts. The first part deletes data from a table called "collections" if a certain condition is met. The condition is that a column called "acquired" must have no value (NULL). The second part of the code retrieves all the data from the "collections" table. The code is used to manage data in a database.

In addition to deleting a single row using a specific condition, SQLite allows deleting multiple rows using the greater than or less than operators. This helps to target specific data that is either less than or greater than a particular value.

DELETE FROM "collections" WHERE "acquired" < '1909-01-01';

---

Foreign Key constraints

Unsafe deletion

_
To delete data from all tables in a database, it's important to first remove any relationships or dependencies between the tables. This can be done by deleting the foreign keys or any other constraints that link the tables. Once this is done, the data can be safely deleted from each table individually.

DELETE FROM "created" WHERE "artist_id" = (
    SELECT "id" FROM "artists" WHERE "name" = 'Unidentified artist'
);

This will delete the id in created table with the artists name of Unidentfied aritist.

DELETE FROM "artists" WHERE "name" = 'Unidentified artist';
SELECT * FROM "artists";

And then we use another command to delete artists name.

---

ON DELETE command

_

Explanation

SQLite's ON DELETE clause is a powerful tool that allows for efficient deletion of data from multiple tables in a single command. By using a foreign key constraint, it's possible to specify what should happen when a row in the referenced table is deleted. The available options include CASCADE, RESTRICT, SET NULL, and SET DEFAULT.

Using the ON DELETE clause can simplify the process of deleting data from multiple tables, and it ensures data integrity by preventing unintentional data loss. However, it's important to use this clause with caution and to choose the appropriate option to avoid data inconsistencies.

FOREIGN KEY ("artist_id") REFERENCES "artists" ("id") 
ON DELETE 

ON DELETE RESTRICT/NO ACTION/SET NULL/SET DEFAULT

schema.sql file

By adding ON DELETE CASCADE to a foreign key constraint, SQLite will automatically delete any related data in all dependent tables when the referenced data is deleted. This helps to maintain data consistency and simplifies the process of deleting related data. It's important to use this clause with caution to avoid unintentional data loss.

Example using ON DELETE function.

SELECT * from "created";

This command will fetch the current data of the created table that has artist_id and collection_id.

Now let's run the command we just learned.

DELETE FROM "artistis" WHERE "name" = 'Unidentified artist';

This will delete the artist_id and collections_id of the artist name 'Unidentified artist'. Deleting all the relation in single command.

---

Conclusion

In SQLite, deleting data can be done using different commands and measures, such as the DELETE command and the DROP TABLE command. It is important to use these commands with caution and to check the syntax and parameters before executing them. To delete data from all tables in a database, foreign keys and other constraints linking the tables must be removed first. SQLite's ON DELETE clause is a powerful tool that allows for efficient deletion of data from multiple tables in a single command, but it must be used with caution to avoid unintentional data loss.