ALTER TABLE and DROP TABLE in SQL

create or replace table rebuilds a table from nothing, so its rows are lost. Once a table holds rows you need, change it in place with ALTER TABLE. Each statement makes one change:

create or replace table supplier_visits (
  visit_id    integer,
  supplier_id integer,
  visited_on  date,
  notes       varchar
);

insert into supplier_visits
values (1, 4, '2024-04-18', 'New orchid line in June.');

alter table supplier_visits add column follow_up boolean default false;
alter table supplier_visits rename column notes to summary;
alter table supplier_visits drop column visited_on;

select * from supplier_visits;

A new column on old rows

A column added with no default is NULL on every row already in the table. With a default, like follow_up above, the existing rows get the default too, and so does every later insert that leaves the column out.

DuckDB can't add a column and a rule in one step: add column ... not null fails with Adding columns with constraints not yet supported. Add the column with its default first, so no row is left empty, then add the rule:

alter table supplier_visits alter column follow_up set not null;

That works here because the default filled every row. On a column that still holds a NULL, it fails with NOT NULL constraint failed, and the table is left as it was. Fill the empty values with an UPDATE first.

Renamed and dropped columns break queries

The ALTER TABLE succeeds and says nothing about the queries that used the old name. The next time one of them runs, it fails with Referenced column "notes" not found in FROM clause! Dropping a column also deletes its values from every row, and there is no undo outside a transaction. Before you rename or drop a column, find what reads it.

DROP TABLE removes the whole table

drop table supplier_visits deletes the table, its rows, and its rules. Run it twice and the second run fails, because the table is gone. IF EXISTS turns a missing table into a no-op, which is what a cleanup script wants:

drop table if exists supplier_visits;

A table that another table's foreign key points at can't be dropped first. In Lesson 8.9's example, drop product_tags, then plant_tags. The DROP statements reference covers views, schemas, and what CASCADE does in DuckDB.