ALTER, DROP, TRUNCATE

As a project grows, its tables change too: a new column is needed, names change, unused tables are removed. Try this lesson’s commands in free play.

ALTER TABLE — changing a table

Add a column:

ALTER TABLE books ADD COLUMN pages int;

Remove a column:

ALTER TABLE books DROP COLUMN pages;

Rename a column:

ALTER TABLE books RENAME COLUMN year TO published_year;

Rename a table:

ALTER TABLE books RENAME TO library_books;

Change a column’s type:

ALTER TABLE books ALTER COLUMN title TYPE varchar(300);

Add a constraint:

ALTER TABLE books ADD CONSTRAINT price_positive CHECK (price >= 0);

DROP — removing

DROP TABLE loans;
DROP DATABASE kutubxona;

DROP cannot be undone: the table and all its data are gone. If you are not sure the table exists: DROP TABLE IF EXISTS loans;.

If another table points to this one through a foreign key, it cannot be dropped — drop the linked (child) table first.

In PostgreSQL DROP TABLE books CASCADE; also removes the foreign keys that point to it. Be careful!

TRUNCATE — a quick clean-out

TRUNCATE TABLE loans;

The table stays but becomes empty. The result is the same as DELETE FROM loans;, but TRUNCATE is far faster (it does not delete rows one by one) and restarts the automatic numbering.

In PostgreSQL, to restart the counter: TRUNCATE loans RESTART IDENTITY;

Which one when?

Remember

• ALTER TABLE … ADD / DROP / RENAME COLUMN changes a table’s structure.
• DROP removes everything, TRUNCATE empties the contents.
• Drop linked tables starting from the child.