Understanding the CockroachDB DROP TABLE Command

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
USE test_db;

CockroachDB DROP TABLE

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

1
DROP TABLE [ IF EXISTS ] TABLE_NAME [, ...] [ CASCADE | RESTRICT ]

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:

PARAMETERDESCRIPTION
IF EXISTSWhen specified, no error will be thrown if the table does no exist.
CASCADEWhen specified, all the objects that depend on the table will be drop.
RESTRICTWhen specified, the table will not be if there is an objects depend on it.

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:

1
 CREATE TABLE numbers ( NUMBER INT );

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

1
2
3
4
5
SHOW TABLES;
  TABLE_NAME
--------------
  numbers
(1 ROW)

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

1
DROP TABLE numbers;

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

1
2
3
  TABLE_NAME
--------------
(0 ROWS)

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:

1
2
3
DROP TABLE numbers;
ERROR: relation "numbers" does NOT exist
SQLSTATE: 42P01

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:

1
DROP TABLE IF EXISTS numbers;

We received no error this time.

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

1
2
3
4
5
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR,
    price INT
);

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

1
2
3
4
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT REFERENCES product (product_id)
);

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:

1
2
DROP TABLE product RESTRICT;
ERROR: "product" IS referenced BY FOREIGN KEY FROM TABLE "orders"

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:

1
DROP TABLE product CASCADE;

Let’s check if the table was dropped successfully:

1
2
3
4
5
SHOW TABLES;
  TABLE_NAME
--------------
  orders
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.