How to Delete a Table in SQL

Sometimes when you’re working with a SQL database, you need to be able to delete an entire table. There can be a lot of reasons why you would need to do this. This is a fairly easy, beginner SQL skill. However, you should know that deleting a table can be dangerous because you will lose data if you are not careful.

First let’s talk about the different ways that you can delete a table in SQL and/or ways to delete data. There are three commands you should focus on primarily: DELETE, TRUNCATE, and DROP.

If you’re looking for specifically how to remove an entire table from the database, we call that “dropping the table”. The short and simple way to do this is:

DROP TABLE [table name here]

Replace “[table name here]” with the name of your table, for example “DROP TABLE tasks“. This will delete the table itself and all of the data in it. If you try to query this table after dropping it, you will get an error that the table does not exist.

Now that we’ve covered the short answer, let’s talk about DELETE, TRUNCATE, and DROP commands in more detail.

Table of Contents

Deleting Data From a Table with the Delete SQL Command

If you need to remove data from a table but leave the table itself in place, you can use the “DELETE” command to do so. The DELETE command is very useful because it allows you to selectively remove data from a table. You can use a WHERE clause with the DELETE command to choose the data you want to remove.

Before you run a DELETE command, you should use the same WHERE clause criteria in a SELECT statement to validate that you’re going to only remove the data that you expect to.

As an example, let’s say I have a a project structure where I have a “projects” table that looks like this:

id (primary key field with auto-increment)
project_name (varchar field)
start_date (datetime field)
end_date (datetime field)

In order to keep track of the tasks in the project, we have a “tasks” table that looks like this:

id (primary key field with auto-increment)
task_name (varchar field)
start_date (datetime field)
end_date (datetime field)
assigned_user_id (a foreign key to the user table)
project_id (a foreign key to the project table)

Let’s say that we created a new project in error. We now want to remove the project and all of its tasks from the database. We can do that by identifying the project ID to be deleted. The we delete all the tasks for that project ID. Finally, we delete the project record from the projects table.

Note that because I have a foreign key relationship from the tasks table to the project table, I have to remove the tasks for the project before I can delete the project record. Otherwise, I will get a foreign key constraint error.

Checking Data Before You Delete It

So the first thing I will do is run a SELECT query with my WHERE clause to make sure I’m getting the right data. For example, if the project ID I want to delete is 123, I would run this query:

SELECT *
FROM tasks
WHERE project_id = 123

Let’s say that there are 10 tasks for this project. I would expect my SELECT query to return 10 results and the project_id field in all of those results should be 123. Once I’ve confirmed that I have the right WHERE clause, I can now delete those records:

DELETE FROM tasks WHERE project_id = 123

This will now remove those tasks. Note that SQL itself isn’t going to warn me that I’m about to delete data. It will assume that I know what I’m doing and just remove it. So we need to be very careful with DELETE queries.

Once I’ve done that, I can now delete my project. Again, I run a SELECT query to see what I’m about to delete:

SELECT *
FROM projects
WHERE id = 123

When I confirm that I get only a single row with an ID of 123, I can then delete my project without issue:

DELETE FROM projects WHERE id = 123

Truncating Data in a SQL Table

Another way to remove data from a SQL table is to “truncate” that table. The TRUNCATE command is used to remove all data from the table. Note that with TRUNCATE, you cannot use a WHERE clause. It will simply remove all data from that table. However, it will respect foreign key constraints, so you must remove any “child” records before removing “parent” records. In other words, from our previous example, you would have to truncate the tasks table before you truncate the projects table. Otherwise you will again see the foreign key constraint error when truncating the projects table first.

The primary advantage that TRUNCATE has is that it will run faster than the DELETE query. However, you cannot roll it back or undo it in any way and as mentioned before, you cannot use a WHERE clause so it’s not able to be used to selectively remove data. It’s intended to be used as a fast way to clean out a table.

In our projects and tasks example, with the structure we outlined above, we don’t need to run SELECT queries first because we’re removing all data. If we want to clean out all projects and start fresh with the database, we would first remove all tasks and then we would remove all projects. Like this:

TRUNCATE TABLE tasks;
TRUNCATE TABLE projects;

If you run these and subsequently run a SELECT statement against either table, you will get no records returned.

Again, be extremely careful with TRUNCATE statements because they are destructive! I always recommend taking a backup of the database before running these queries so that if something goes wrong you can restore the backup and minimize data loss.

How to Delete a Table in SQL with DROP Statements

As we mentioned at the very beginning, sometimes you want to remove a table completely from the database. There could be a lot of reasons for this, but you should be extremely careful with this because it will remove not only the data, but the table itself. That means any queries or software applications that rely on the table will fail immediately after the table is removed. When we need to delete a table in SQL, we call that “dropping the table”. We use the DROP statement to accomplish that.

Note again, that the database will respect referential integrity of the table before deciding if you can drop it or not. Again using our projects and tasks structure as an example, if I try to drop the projects table before I drop the tasks table, I’ll get a foreign key constraint error. You would need to either remove the foreign key constraint or drop the other table first.

If we’re trying to drop the entire data structure, we can start by dropping the “child” tables that have foreign keys pointing back to the “parent” tables.

DROP TABLE tasks;
DROP TABLE projects;

This query will run successfully because when we drop the tasks table, it will by default remove the foreign key relationship to the projects table. When it reaches the statement to drop the projects table, there is no longer a foreign key constraint.

Deleting a SQL Table with Foreign Key Constraints

If we want to leave the tasks table but remove the projects table – this would be unusual, but stranger things have happened – we would want to use the DROP statement to remove the foreign key itself. That’s right – DROP can be used to remove virtually any type of database object such as a table, a column, a foreign key, an index, a view, a stored procedure, or a function! In some cases, we have to use the ALTER TABLE statement to indicate that we’re dropping a part of a table.

To keep our tasks table but drop our projects table, we would run the following two SQL queries:

ALTER TABLE tasks
DROP CONSTRAINT FK_tasks_projects;
DROP TABLE projects;

This will run successfully. The first statement tells SQL that we are going to modify, or ALTER, the tasks table and then instructs it to drop the “FK_tasks_projects” foreign key constraint. Once that is gone, the database no longer enforces the relationship between the tasks and projects table. We can now drop our projects table.

Just a note about this situation though: What we have just done is “orphaned” the records in the tasks table. We no longer know anything about the projects that those tasks belong to. We can’t query the projects table and we can’t join it to the tasks table anymore because it doesn’t exist. Be extremely careful about how you use these statements. SQL will let you do things, as long as you provide the right commands in the right order, that you may not actually want to do.

And again, always take backups before you run destructive queries like this!

Conclusion

SQL gives you several ways to remove data. These methods allow you to remove specific records selectively using the DELETE command. You can remove all data in a table using the TRUNCATE command. And you can remove all the data and the table structure itself using the DROP command. You can also remove pieces of a table, such as a column or foreign key constraint, using the ALTER command in conjunction with the DROP command.

We should always be very careful using these commands because they can be destructive to our database. Take backups and test your code in a database where you won’t lose your job if you lose the data!

Happy coding!