Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Data Definition Language (DDL)  ›  Capstone Project

DROP TABLE

Drop Table 12 minDROP TABLE permanently removes a table's rows and structure, and dependent objects such as foreign keys either block it or must be dropped with it.

DROP TABLE

A shop ran a one-off promotion and created a promo_signups table for it. The promotion is over and the table is clutter. DROP TABLE removes it — not just the rows, but the table's very existence. It is the most final statement in this course, which is why it deserves its own short rule and a habit of pausing before you press Enter.

The rule

DROP removes the table, its rows and its shape — no undo.

DROP TABLE promo_signups;

After this there is no promo_signups table at all. Any query that mentions it fails with "table does not exist". Its columns, its data, its indexes and its constraints are gone together. Short of a backup (or, in PostgreSQL, a transaction you have not committed yet), nothing brings it back.

Two variations matter:

DROP TABLE IF EXISTS promo_signups;   -- no error if it is already gone
DROP TABLE customers CASCADE;         -- PostgreSQL: also drop things that depend on it

IF EXISTS makes cleanup scripts safe to re-run. CASCADE needs care. In PostgreSQL it drops dependent objects such as views built on the table, and removes foreign key constraints in other tables that point at it. It does not delete the orders table itself, but after it, orders no longer has any rule tying it to customers.

Without CASCADE, the database protects you, and that protection is a feature, not an obstacle: while orders has a foreign key pointing at customers, a plain DROP TABLE customers; is refused.

The shortcut

Before any DROP, run a two-question pre-flight check:

  1. Who depends on it? Foreign keys, views and application code that read this table.
  2. Where is the backup? If the answer is "nowhere", do not drop.
Pre-flight for dropping promo_signupsStep 1 of 4

  1. Who depends on it?

    No foreign keys point at it and no views use it. The app code that wrote to it was removed last week.

The trap is dropping when you meant to empty. If the table will be used again, you want TRUNCATE (or DELETE), not DROP.

Drills

Answer fast — trust the rule.

quick drill

After DROP TABLE promo_signups;, what does SELECT * FROM promo_signups; do?

quick drill

A cleanup script might run when the table has already been dropped. Which statement avoids an error?

quick drill

orders has a foreign key referencing customers. What does a plain DROP TABLE customers; do?

quick drill

You want to reuse orders for a fresh test run but remove every row in it. Which rung of the ladder?

Remember

  • DROP removes the table, its rows and its shape — no undo.
  • Pre-flight: who depends on it, and where is the backup?
  • Emptying is TRUNCATE; removing for good is DROP. Do not mix them up.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainClimb the ladder correctly

For each request, name the statement you would use — DELETE, TRUNCATE or DROP — and explain why: (a) remove last year's cancelled orders; (b) clear a staging table before reloading it; (c) get rid of a table from a feature that has been removed.