ALTER, DROP, TRUNCATE, identity columns and sequences

Part of the free LogicWiz SQL & Databases course, Module 7: Creating tables. The queries and practice run in the app on real Postgres.

The app has 2 lakh users, and the product team wants a referral_code column by tonight. You cannot drop the table and start again. You ALTER it.

You'll learn to

• Change a live table with ALTER TABLE (add, rename, retype, constrain)
• Know when to use DROP, TRUNCATE and DELETE
• Number rows automatically with identity columns and sequences
• Explain why ids have gaps

Learn

ALTER TABLE: change a table that has data

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(40), phone VARCHAR(10));
INSERT INTO customers VALUES (1, 'Aarav', '9000000001'), (2, 'Meher', '9000000002');

ALTER TABLE customers ADD COLUMN referral_code VARCHAR(10);
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers ALTER COLUMN name TYPE VARCHAR(80);
ALTER TABLE customers ALTER COLUMN name SET NOT NULL;
ALTER TABLE customers ADD CONSTRAINT referral_code_unique UNIQUE (referral_code);

SELECT * FROM customers;

The rows survive every change. The new column is NULL for existing rows. SET NOT NULL and ADD CONSTRAINT first check the existing rows and fail if any row breaks the rule.

A required column on a table with rows

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);
INSERT INTO customers VALUES (1, 'Aarav'), (2, 'Meher');

ALTER TABLE customers ADD COLUMN plan VARCHAR(10) NOT NULL;

The two existing rows would get NULL, which NOT NULL forbids: column "plan" contains null values. Give it a DEFAULT: ADD COLUMN plan VARCHAR(10) NOT NULL DEFAULT 'free' fills every existing row and works.

DELETE vs TRUNCATE vs DROP

In Postgres all three can be rolled back inside a transaction. In MySQL and Oracle, TRUNCATE and DROP commit straight away.

Identity columns: let Postgres number the rows

CREATE TABLE tickets (
  ticket_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  passenger  VARCHAR(40) NOT NULL
);

INSERT INTO tickets (passenger) VALUES ('Riya'), ('Kabir'), ('Sneha');

SELECT * FROM tickets;

Leave the id out and Postgres fills 1, 2, 3. GENERATED ALWAYS refuses an id you type by hand; GENERATED BY DEFAULT accepts one. The older shortcut SERIAL does the same job; identity is the SQL-standard way.

Sequences: the counter behind it

CREATE SEQUENCE token_no START WITH 1001;

SELECT nextval('token_no') AS first_token,
       nextval('token_no') AS second_token;

A sequence is a counter object. nextval gives the next number and moves the counter on, even if the transaction later rolls back. Identity columns use one behind the scenes, like a token machine at a bank: a token taken is never given again.

Ids from a sequence have gaps: a failed INSERT, a ROLLBACK or a DELETE all leave holes, and TRUNCATE without RESTART IDENTITY carries on from the old number. Never use an identity as a gap-free invoice or receipt number, and never compute "number of rows" as MAX(id).

MySQL: id INT AUTO_INCREMENT PRIMARY KEY instead of identity. Changing a type is ALTER TABLE t MODIFY COLUMN name VARCHAR(80). TRUNCATE always resets AUTO_INCREMENT. Oracle uses GENERATED AS IDENTITY like Postgres.

"Difference between DELETE, TRUNCATE and DROP" is asked in almost every DBMS viva and SQL interview. Say four things: what is removed, whether WHERE works, whether the structure stays, and DML vs DDL.

In one line

ALTER TABLE changes a live table; TRUNCATE empties it, DROP removes it; identity columns number rows for you, with gaps.

Interview corner

What is the difference between DELETE, TRUNCATE and DROP?

DELETE is DML: it removes the rows matching a WHERE, one by one, and fires row triggers. TRUNCATE is DDL: it empties the whole table at once, much faster, and keeps the structure. DROP removes the table itself, structure and all. In Postgres all three are transactional; in MySQL/Oracle, TRUNCATE and DROP commit immediately.

SERIAL vs IDENTITY in Postgres?

Both number rows from a sequence. SERIAL is an old Postgres shortcut that creates a sequence and a default. GENERATED ALWAYS AS IDENTITY is the SQL standard, ties the sequence to the column properly, and with ALWAYS blocks hand-typed ids. Prefer identity in new tables.

Why do auto-increment ids have gaps?

A sequence hands out numbers outside the transaction so that concurrent inserts never wait for each other. If an insert fails or rolls back, its number is used up. Deletes also leave holes. So ids are unique, not gap-free.

How do you add a NOT NULL column to a table that already has rows?

Give it a DEFAULT in the same statement: ALTER TABLE users ADD COLUMN plan VARCHAR(10) NOT NULL DEFAULT 'free'. Without a default the existing rows would be NULL and the ALTER fails. In Postgres 11+ a constant default is added instantly, without rewriting the table.