Dropping tables using DROP TABLE

Now let's talk about how to properly drop tables from your database using the DROP TABLE command.

DROP TABLE is a command that lets you delete a table from your database along with all its data and structure. Imagine you wrote your thesis project and then accidentally burned it along with your laptop. That's kinda what using DROP TABLE is like. Sounds scary? Don't worry, the main thing is to understand how it works and be careful!

Features of DROP TABLE:

• Deletes the whole table (and its data) with no way to get it back (unless you made a backup).
• Completely frees up the space the table took up on disk.
• Also deletes all related indexes, constraints, and triggers.

Syntax of the DROP TABLE command

The DROP TABLE command has a simple and clear syntax:

DROP TABLE table_name;

Where:

• table_name — the name of the table you want to drop.

Simple example of dropping a table

Let's look at an example. Suppose we have a table called students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age > 0)
);

If you don't need this table anymore, you can drop it with the command:

DROP TABLE students;

After running this command, the students table will be completely gone from the database, including all the student data (that might have been in it).

Dropping multiple tables

You can also drop several tables in one go. Just list them separated by commas:

DROP TABLE table1, table2, table3;

Example:

DROP TABLE students, teachers, courses;

This command will drop three tables at once: students, teachers, and courses.

Using IF EXISTS

The DROP TABLE command will throw an error if you try to drop a nonexistent table. To avoid this error, you can use the IF EXISTS option:

DROP TABLE IF EXISTS table_name;

Example:

DROP TABLE IF EXISTS students;

If the students table exists, it'll be dropped. If it doesn't, the command will run without an error and just give you a warning.

Dropping tables with dependencies

Sometimes dropping tables can be tricky if other tables or objects depend on them. For example, if a table has foreign keys linked to other tables. I'll talk more about foreign keys in the next level :P

Let's say you have two tables: students and enrollments. The enrollments table depends on students because it references it with a foreign key.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(id), -- column student_id references students.id
    course_name VARCHAR(100)
);

If you try to drop the students table without first dropping the enrollments table, you'll get an error:

DROP TABLE students;
-- ERROR:  cannot drop table "students" because other objects depend on it
-- DETAIL:  constraint enrollments_student_id_fkey on table enrollments depends on table students

To drop a table and all dependent objects, use the CASCADE option:

DROP TABLE students CASCADE;

This command will drop the students table and automatically drop all objects that depend on it (in this case, the enrollments table).

But be careful with CASCADE, since it can drop more objects than you expected. To see what objects will be dropped, use CASCADE and then check the logs :)

Dropping tables without dropping dependent objects

If you want to prevent accidentally dropping related objects, use the RESTRICT option. This option blocks dropping a table if other objects depend on it:

DROP TABLE students RESTRICT;

If the table has dependent objects, the command will throw an error and cancel the drop operation.

Temporary tables and dropping them

Temporary tables (created with CREATE TEMP TABLE) are automatically dropped when the database session ends. But you can drop a temporary table manually during the session using DROP TABLE.

Example:

CREATE TEMP TABLE temp_data (
    id SERIAL PRIMARY KEY,
    value TEXT
);

DROP TABLE temp_data;

Common mistakes when using DROP TABLE

Trying to drop a nonexistent table. To avoid an error, use IF EXISTS.

DROP TABLE IF EXISTS non_existing_table;

Dropping a table with dependencies without using CASCADE. If other tables are linked to the table you're trying to drop, the command will fail with an error. Use CASCADE if you're sure you want to drop all dependencies.

DROP TABLE students CASCADE;

Overusing CASCADE. This can lead to dropping critical objects. Before using CASCADE, make sure you understand what objects will be affected.

Accidentally dropping a table. Always double-check what you're dropping, especially in production databases.

Handy tips, some of them kinda serious

Handy tip 1: deny everything

Delete the traces faster than the data.
And then confidently say: "That was just an anomaly in the logs. I already fixed it."

Pretend it's a feature.
"We decided to run a failover stress test... And yeah, the system didn't handle it. But now we know what to improve!"

Announce an evacuation: "We have a production-level incident."
When everyone's running around and panicking, nobody has time to ask who exactly hit DELETE FROM without WHERE.

Update your resume on hh.ru as a DBA.
Now you're really closer to databases. Especially the ones that don't exist anymore.

Schedule a coffee break in advance.
Because in a few minutes, you might be offered to "take a break from the project... forever."

Pretend it was staging.
"Are you sure that wasn't the test environment? We were testing something... probably..."

Sit right in the middle of the open space.
Nobody will believe the culprit would dare sit that openly.

Blame ChatGPT.
It's all artificial intelligence. As you can see, it's not ready to replace you yet. Please don't fire me.

Handy tip 2: how to job hunt with a tarnished reputation

• Drop all tables at once. Then management will be too busy to fire anyone personally.

• No one gets blamed alone. If you take your boss down with you—no bad references anymore.

• Dropped client data too? Congrats, now the company has bigger problems than just firing you.

Handy tip 3: backup before dropping

And now—no jokes.

Before you drop a table (especially if it has important data), make a backup using the pg_dump command. This will let you restore the data if you accidentally dropped something you shouldn't have.

Example backup command:

pg_dump -U username -d database_name -t table_name > table_backup.sql

At this point, you've learned all the basics of working with the DROP TABLE command. Keep this "nuclear button" to yourself and only use it when you're sure about what you're doing. In the next lecture, we'll keep learning about changing the structure of tables and other database objects.