Creating and Altering Tables
Design tables with CREATE TABLE, modify them with ALTER TABLE, and drop safely.
What you'll learn
- CREATE TABLE syntax with columns and data types
- Common column options: NOT NULL, DEFAULT, UNIQUE, CHECK
- ALTER TABLE to add, rename, and drop columns
- DROP TABLE and the danger it carries
- Best practices for table and column naming
Concept
CREATE TABLE
CREATE TABLE defines a new table with its columns, data types, and constraints. Each column declaration specifies a name, a type, and optional modifiers like NOT NULL, DEFAULT, UNIQUE, or CHECK. The table name and column names should be descriptive, lowercase, and use underscores (order_items, not OrderItems).
Column Modifiers
- NOT NULL — the column must always have a value; never NULL
- DEFAULT — a value used when the column is omitted from INSERT
- UNIQUE — no two rows can have the same value in this column
- CHECK (condition) — the value must satisfy a boolean expression
- REFERENCES table(col) — foreign key constraint
These modifiers are constraints that the database enforces on every insert and update. They are your first line of defense against bad data.
ALTER TABLE
ALTER TABLE modifies an existing table. You can add columns (ADD COLUMN), drop columns (DROP COLUMN), rename columns (RENAME COLUMN), change data types (ALTER COLUMN ... TYPE), and add or remove constraints. In production, altering a large table can be expensive and may briefly lock it — plan migrations carefully.
DROP TABLE
DROP TABLE permanently removes the table and all its data. There is no undo (unless you are inside a transaction). DROP TABLE IF EXISTS avoids an error if the table does not exist. Always double-check before dropping — this is the most destructive SQL command.
Naming Conventions
Use singular, lowercase, snake_case names: user or users, order_item or order_items. Pick one convention (singular vs plural) and stick with it across the project. Primary keys are typically named id; foreign keys are <referenced_table>_id (e.g. user_id).
Example
-- Create a table with various column modifiers
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
category VARCHAR(50) NOT NULL DEFAULT 'misc',
sku VARCHAR(50) UNIQUE,
in_stock BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Add a new column
ALTER TABLE products ADD COLUMN weight DECIMAL(6, 2);
-- Rename a column
ALTER TABLE products RENAME COLUMN sku TO product_code;
-- Drop a column
ALTER TABLE products DROP COLUMN weight;
-- Drop the table (destructive!)
DROP TABLE IF EXISTS products;
A complete table definition with six column modifiers, then three ALTER TABLE operations and a safe DROP TABLE IF EXISTS.
Try it
- SQL Formatter
Format CREATE and ALTER TABLE statements.
- Code Diff
Compare old and new schema definitions side by side.
Common mistakes
The mistake
Using FLOAT or REAL for monetary values
The fix
Floating-point types introduce rounding errors. Use DECIMAL(p, s) for money — e.g. DECIMAL(10, 2) stores up to 99,999,999.99 exactly.
The mistake
Running DROP TABLE without IF EXISTS
The fix
If the table does not exist, DROP TABLE errors. Use DROP TABLE IF EXISTS to make scripts idempotent and safe to re-run.
Practice Creating and Altering Tables
2 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.