SQL CREATE TABLE, constraints and views
Good queries start with good tables. A few keywords decide what data is allowed in, and the database then enforces those rules for you, forever. Every example here runs on a throwaway copy, so experiment freely.
Creating a table
List each column with a type and optional constraints. PRIMARY KEY identifies each row (unique, never NULL). NOT NULL makes a column required. DEFAULT fills in a value when an insert leaves it out.
CREATE TABLE volunteers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, hours INTEGER DEFAULT 0 ); INSERT INTO volunteers (id, name) VALUES (1, 'Ana'), (2, 'Ben'); SELECT * FROM volunteers;
UNIQUE and CHECK
UNIQUE forbids duplicate values. CHECK (condition) rejects rows that break a rule. Here the duplicate email is skipped by ON CONFLICT DO NOTHING because UNIQUE flags it.
CREATE TABLE newsletter ( id INTEGER PRIMARY KEY, email TEXT UNIQUE, age INTEGER CHECK (age >= 13) ); INSERT INTO newsletter VALUES (1, 'ana@mail.com', 30); INSERT INTO newsletter VALUES (2, 'ana@mail.com', 25) ON CONFLICT DO NOTHING; SELECT * FROM newsletter;
Foreign keys
A foreign key column points at another table's primary key with REFERENCES. It keeps relationships valid: no visit for a pet that doesn't exist. (SQLite enforces this only with PRAGMA foreign_keys = ON.)
CREATE TABLE vet_visits ( id INTEGER PRIMARY KEY, pet_id INTEGER NOT NULL REFERENCES pets(id), visited_on DATE NOT NULL ); INSERT INTO vet_visits VALUES (1, 4, '2026-11-02'), (2, 7, '2026-11-03'); SELECT v.visited_on, p.name FROM vet_visits v JOIN pets p ON p.id = v.pet_id;
Changing a table with ALTER
ALTER TABLE … ADD COLUMN adds a column; existing rows get its DEFAULT. PostgreSQL can also drop and retype columns freely; SQLite supports ADD, RENAME and DROP COLUMN but rebuilds the table for bigger changes.
ALTER TABLE pets ADD COLUMN microchipped BOOLEAN DEFAULT FALSE; SELECT name, microchipped FROM pets LIMIT 5;
Views
CREATE VIEW saves a query under a name. It stores the query, not the data, so it always reflects the current rows. Views make complex logic reusable and give people a simpler interface.
CREATE VIEW waiting_pets AS SELECT name, species, age FROM pets WHERE adopted = false; SELECT * FROM waiting_pets ORDER BY age DESC;
Common mistakes
Tables without a primary key, which makes duplicates and updates painful.
Copying the same fact into several tables instead of referencing it by id.
Choosing TEXT for dates or money in PostgreSQL. Use DATE and NUMERIC so the database can check and compute them.
Questions
INTEGER PRIMARY KEY vs SERIAL vs IDENTITY?+
In SQLite, INTEGER PRIMARY KEY auto-numbers. In PostgreSQL, use id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY (SERIAL is the older form).
What is normalisation?+
Storing each fact exactly once and linking with keys, so data can't contradict itself.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.