sqlstreak

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.

Practise it now