DDL: CREATE, ALTER, DROP

Changing the Structure, Not the Data

DDL (Data Definition Language) manages tables and other schema objects.

CREATE TABLE

CREATE TABLE customers (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers MODIFY COLUMN email VARCHAR(200);
ALTER TABLE customers DROP COLUMN phone;
ALTER TABLE customers ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');

The exact ALTER syntax varies by database; MySQL uses MODIFY, PostgreSQL uses ALTER COLUMN ... TYPE, and so on.

DROP and TRUNCATE

DROP TABLE customers;            -- deletes table and all data
TRUNCATE TABLE customers;       -- deletes all rows, keeps the table
DROP DATABASE old_shop;         -- whole database gone

DROP removes the object entirely; TRUNCATE empties it but keeps its structure. Both are destructive, so use them with care.

Key Points

• CREATE adds structure; ALTER changes it; DROP removes it.
• TRUNCATE clears rows but keeps the table.
• Always review queries before running DROP on real data.
• Dialect differences in ALTER syntax are expected.