SQL: Dropping Tables

• Sometimes an entire table is no longer needed. We could DELETE all data in it, but the table would still exist. Over time, we'd end up with many unused, empty tables in our database. It's better to completely destroy the table.

• We remove tables by DROPing them. If we DROP a table, trying to access it in any way is an error because it no longer exists.

• >

  exec(`CREATE TABLE users (email TEXT, name TEXT)`);
  exec(`DROP TABLE users`);
  exec(`SELECT * FROM users`);

  Result:

  Error: no such table: users

• Here's a code problem:

  Drop the cats table.

  9

  1

  2

  exec(`CREATE TABLE cats (name TEXT, age INTEGER)`);

  exec(`

  11

  3

  DROP TABLE cats

  12

  4

  5

  6

  7

  8

  `);

  exec(`

  INSERT INTO cats (name, age)

  VALUES ('Ms. Fluff', 3);

  `);

  Goal:

  Error: no such table: cats

  Yours:

  Error: no such table: cats

  Show Author's Answer

• What's the difference between deleting and dropping? Deleting is always about data: it removes rows. Dropping is always about database structure: it removes tables. In most database systems, we can also drop a single column from a table. Later in this course, we'll see examples of dropping other structures as well.