Understanding the CockroachDB DROP TABLE Command

Introduction

When you’re using CockroachDB to store your data, there may be times when you need to remove a table from your database. It’s important to know how to handle this task correctly, since deletions are permanent and cannot be undone. The CockroachDB DROP TABLE statement offers a simple way to delete a table from the database. In this article, we’ll take a closer look at the CockroachDB DROP TABLE command and learn how to use this SQL statement to delete a table.

Prerequisites

Before we go any further with this tutorial, let’s review a few key prerequisites for the task:

• You’ll need to have CockroachDB installed on your machine.

• In order to follow along with our examples, you should have a basic knowledge of CockroachDB. You can connect to a CockroachDB database using the CockroachDB command-line interface: cockroach sql.

• The \c command that you may be familiar with from psql will not work in the CockroachDB SQL interface. You can execute the USE command instead to connect to the database:

CockroachDB DROP TABLE

Let’s begin by looking at the syntax for the DROP TABLE statement:

SQL DROP TABLE Statement

Since CockroachDB is based on Postgres, it’s no surprise that the DROP TABLE statement conforms to standard SQL syntax. The table below shows some options that can be used with the DROP TABLE statement:

CockroachDB DROP TABLE Examples

Let’s create a new table that we won’t mind deleting in our DROP TABLE examples. We can do this using the CREATE TABLE statement:

To see the table listed in your database, use the SHOW TABLES statement:

To drop this table, we’ll use the DROP TABLE statement:

The table should be deleted from the database, Let’s confirm:

Now that we have dropped the table, if we try to drop the table again we will get an error because it no longer exists in the database. To avoid this, we can use the IF EXISTS clause to check if the table still exists. We specify this option after the DROP TABLE statement and before the name of the table.

First, we’ll try to drop a table without IF EXISTS and see what we get:

We received an error because the table named numbers is already dropped from the database. Now, let’s try again, but this time we will specify the IF EXISTS clause:

We received no error this time.

Let’s create another sample table to use in our next example. We’ll name it “product”:

Then we’ll create another table that will have a relationship to the “product” table:

Using the RESTRICT option, we can ensure that a table won’t be dropped if it has objects that depend on it. For example, we’ll try to drop the product table that has another table depending on it:

If you get an error that says "referenced by foreign key", that means another object depends on it.

To allow the deletion of a table regardless of its dependencies, we can use the CASCADE option at the end of the DROP TABLE statement:

Let’s check if the table was dropped successfully:

We can see that the product table is now dropped.

Conclusion

If you need to delete a table in CockroachDB, it’s important to proceed with care. Deleting a table is a permanent operation, and you also need to consider any dependencies that may exist for the table. The CockroachDB DROP TABLE statement makes it easy to delete a table and provides options that allow you to handle potential dependencies. In this article, we showed you how to use the DROP TABLE statement and provided plenty of examples to get you started. With our examples to guide you, you’ll be able to remove tables safely and effectively from your own CockroachDB database.