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 theUSE
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:
PARAMETER | DESCRIPTION |
---|---|
IF EXISTS | When specified, no error will be thrown if the table does no exist. |
CASCADE | When specified, all the objects that depend on the table will be drop. |
RESTRICT | When 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