CREATE TABLE and constraints

Why it matters

Every table you have queried was created by someone who chose its columns, types and rules. Those choices decide what data can get in. A well-designed table rejects a missing email, a duplicate username or a negative price before it ever reaches a report. Developers write these definitions in migrations, and database administrators review them, so being able to read and write CREATE TABLE is a core skill.

The basic form

CREATE TABLE members (
  id        INTEGER PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,
  name      TEXT NOT NULL,
  plan      TEXT NOT NULL DEFAULT 'free',
  joined_on TEXT NOT NULL
);

Each line is a column: a name, a type, then optional constraints. Commas separate the columns; there is no comma after the last one.

Choosing types

Common types across engines:

Two notes. First, for money, prefer an exact NUMERIC/DECIMAL type where the engine has one, or store whole cents as integers: floating-point types like REAL can show tiny rounding errors (0.1 + 0.2 is not exactly 0.3). Second, SQLite is unusually relaxed about types: by default it will store text in an INTEGER column. Since version 3.37 you can add STRICT after the closing parenthesis to make it enforce types like other engines. Constraints, below, are enforced either way.

Constraints

• PRIMARY KEY: unique and identifies the row. In SQLite, INTEGER PRIMARY KEY also auto-numbers new rows. PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or the older SERIAL), MySQL AUTO_INCREMENT.
• NOT NULL: the column must have a value. Make this the default for anything the application needs; leaving it out allows NULL.
• UNIQUE: no two rows may share the value. Use it for emails, usernames and codes.
• DEFAULT value: used when an INSERT leaves the column out. Here, new members get 'free'.
• CHECK (condition): any rule you can write as a condition: CHECK (price >= 0), CHECK (plan IN ('free', 'pro')).
• REFERENCES other(col): a foreign key, as in module 3.

Constraints that involve several columns go at the end, after the column list:

CREATE TABLE enrollments (
  student_id  INTEGER NOT NULL REFERENCES students(id),
  course_id   INTEGER NOT NULL REFERENCES courses(id),
  enrolled_on TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

Seeing a constraint work

INSERT INTO members (email, name, joined_on)
VALUES ('ana@example.com', 'Ana', '2024-02-01');   -- ok, plan becomes 'free'

INSERT INTO members (email, name, joined_on)
VALUES ('ana@example.com', 'Ana again', '2024-02-02');
-- Error: UNIQUE constraint failed: members.email

The second insert is refused and the table is unchanged. That error is the schema doing its job.

Inspecting a table

Each engine can describe a table's columns. In SQLite, the pragma_table_info function returns one row per column:

SELECT name, type, "notnull", dflt_value, pk
FROM pragma_table_info('members');

PostgreSQL uses \d members in its psql tool or the information_schema.columns view; MySQL uses DESCRIBE members.

Changing and removing tables

• ALTER TABLE members ADD COLUMN phone TEXT; adds a column. Engines differ in what else ALTER TABLE can do; SQLite supports adding, renaming and dropping columns but not changing a column's type.
• DROP TABLE members; deletes the table and all its data. DROP TABLE IF EXISTS avoids an error if it is already gone.
• CREATE TABLE IF NOT EXISTS skips creation when the table exists.

On a shared or live database, these statements belong in a reviewed migration script, never typed by hand.

Naming conventions

Pick a style and stick to it. A common one: lowercase snake_case, plural table names (members), id for the primary key, and <thing>_id for foreign keys (member_id). Avoid spaces and reserved words such as order or group as names.

Common mistakes

• A trailing comma after the last column.
• Forgetting NOT NULL, so required data can be missing.
• Floating-point types for money.
• Dates in non-ISO text such as '02/01/2024'.
• Reserved words as names, which then need quoting everywhere.
• Dropping a table to "start fresh" on a database other people use.

Quick recap

• CREATE TABLE defines columns, types and constraints.
• PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK and REFERENCES keep bad data out.
• Types differ by engine; SQLite is flexible unless you use STRICT.
• ALTER TABLE and DROP TABLE change schemas; use migrations on shared databases.

Try it

Open the Do tab and define a members table with the right types and constraints.