SQL / DEFINING TABLES AND CONSTRAINTS
Creating tables that hold the shape you mean
Write CREATE TABLE statements whose column list, order, and constraint placement pin down exactly which rows the table will accept.
What you will learn
- Read a CREATE TABLE body as one list mixing column definitions and table constraints
- Choose column-level or table-level placement by how many columns the rule spans
- Name table-level constraints so violation messages point back at the rule you wrote
- Check a live table's declared shape from the catalog, not from the script you think ran
Understanding Creating tables that hold the shape you mean
Everything between the parentheses of CREATE TABLE is one comma-separated list, and each item in it is either a column definition or a table constraint. A column definition is a name, then a type, then any number of constraints with no commas separating them, which is why `seats_taken INTEGER NOT NULL DEFAULT 0` is a single item and an extra comma would turn `NOT NULL` into a nonsense column name. The order of the column definitions is not cosmetic: it becomes the stored column order, the order `SELECT *` returns, and the order a `VALUES` list without a column list has to match.
The statement is a declaration the engine enforces on every future write, but it is not a check on your judgment. Anything that parses gets created, so a table of six nullable text columns is exactly as legal as a tight one. It helps to read a definition as the set of rows the table will ever hold: each type and constraint you write removes rows from that set, and each loose column leaves them in. Whatever the definition does not rule out becomes application code you have to write, or data someone cleans up later.
The same rule can often be written in two places, and placement decides scope rather than style: attached to a column it constrains that one column, while a rule that mentions two columns can only be written as a separate table-level item. Once the table exists, editing your CREATE TABLE and rerunning it changes nothing — `IF NOT EXISTS` skips silently and a plain `CREATE TABLE` fails on the name — so the shape in the database and the shape in your file drift apart. Read the shape back from the catalog (`pragma_table_info` in SQLite, `information_schema.columns` elsewhere) before you trust it.
CREATE TABLE workshop_booking (
id INTEGER PRIMARY KEY,
room_code TEXT NOT NULL,
starts_on TEXT NOT NULL,
seats_taken INTEGER NOT NULL DEFAULT 0,
CONSTRAINT one_booking_per_room_per_day UNIQUE (room_code, starts_on)
);
INSERT INTO workshop_booking (room_code, starts_on, id)
VALUES ('B12', '2026-03-04', 1);
-- no column list, so these values must line up with the declared order
INSERT INTO workshop_booking VALUES (2, 'B12', '2026-03-05', 4);
SELECT * FROM workshop_booking;A CREATE TABLE body is one ordered list of column definitions and table constraints, and together they define the exact set of rows the table will ever accept.
Worked examples
Reading the shape back
Shows that the declared column order, types, and per-column flags are stored metadata you can query.
CREATE TABLE sensor_reading (
reading_id INTEGER PRIMARY KEY,
sensor_name TEXT NOT NULL,
celsius REAL,
taken_at TEXT NOT NULL DEFAULT '1970-01-01'
);
SELECT cid, name, type, "notnull", dflt_value
FROM pragma_table_info('sensor_reading');Example explained
Line 1`taken_at TEXT NOT NULL DEFAULT '1970-01-01'` is one list item: a name, a type, then two constraints with no commas between them.
Line 2`cid` is the declared position, so it tells you the order `SELECT *` and positional INSERT will use.
Line 3`dflt_value` holds the default as SQL source text, which is why the date comes back with its quotes.
Line 4An empty `dflt_value` cell is NULL, meaning no default was declared for that column.
Where a constraint sits changes what it means
Demonstrates that a rule written beside a column covers only that column, while a two-column rule must be a table-level item.
CREATE TABLE tag_a (post_id INTEGER, tag TEXT UNIQUE);
CREATE TABLE tag_b (post_id INTEGER, tag TEXT, UNIQUE (post_id, tag));
INSERT INTO tag_b VALUES (1, 'sql'), (2, 'sql');
SELECT post_id, tag FROM tag_b;
INSERT INTO tag_a VALUES (1, 'sql'), (2, 'sql');Example explained
Line 1`tag TEXT UNIQUE` is part of a column definition, so its scope is that single column.
Line 2`UNIQUE (post_id, tag)` is a separate list item because a rule naming two columns cannot be attached to either one.
Line 3The insert into tag_b succeeds: the pairs (1,'sql') and (2,'sql') are different pairs.
Line 4The identical rows fail in tag_a because 'sql' repeats inside the one column the rule covers.
IF NOT EXISTS does not reshape
Shows that rerunning a widened CREATE TABLE against an existing name leaves the old shape in place.
CREATE TABLE IF NOT EXISTS note (note_id INTEGER PRIMARY KEY, body TEXT NOT NULL);
-- same name, wider shape, run second
CREATE TABLE IF NOT EXISTS note (note_id INTEGER PRIMARY KEY, body TEXT NOT NULL, created_on TEXT);
SELECT count(*) AS column_count FROM pragma_table_info('note');
INSERT INTO note VALUES (1, 'first', '2026-01-01');Example explained
Line 1The second statement sees the name is already taken and does nothing at all; it does not compare definitions.
Line 2`count(*)` over pragma_table_info reports 2, the shape created by the first statement.
Line 3The three-value insert then fails on column count, which is usually how the skipped statement gets noticed.
Line 4Widening an existing table needs ALTER TABLE or a deliberate drop and rebuild, not a rerun.
Important notes
Examples run on SQLite; other engines differ in error wording and catalog access, and Postgres reports the violated constraint by name.
In most engines ALTER TABLE ADD COLUMN appends to the end, so a column order you regret costs a table rebuild rather than a quick edit.
Common mistakes
Putting a comma between a column's type and its constraints, as in `room_code TEXT, NOT NULL` — the parser reads NOT NULL as the start of a new column definition and the statement dies with a syntax error.
Editing a `CREATE TABLE IF NOT EXISTS` script and rerunning it, then assuming the new column is there; the statement is skipped and inserts fail later with a column-count error.
Relying on positional `INSERT INTO t VALUES (...)` when two neighbouring columns share a type; swapped arguments land in the wrong columns and nothing raises an error.
Try it yourself
Change, predict, then run
In a browser SQLite editor, create a `seat_reservation` table with an id, a screening id, a row letter, and a seat number, plus one named table-level constraint that stops the same seat being reserved twice for the same screening. Prove the shape holds by inserting the same seat for two different screenings, then the same seat twice for one screening.
Open the SQL workspaceCheck your understanding
A table is defined as `CREATE TABLE booking (room TEXT, day TEXT, UNIQUE (room, day));` and a colleague rewrites it as `CREATE TABLE booking (room TEXT UNIQUE, day TEXT UNIQUE);`. What changes about the rows the table accepts?
- Nothing changes; the two forms are the same rule written differently
- The rewrite also rejects a second booking for the same room on a different day
- The rewrite now allows the same room and day pair to repeat
- Only the error text changes, because both forms build the same index
Show answer
A rule written inside a column definition scopes to that column, so `room TEXT UNIQUE` lets 'B12' appear once in the entire table and blocks every later day for that room; the original only forbade the exact (room, day) pair from repeating. Option 0 is tempting because the keyword is identical in both versions, but moving a rule between column level and table level changes its scope, not just its spelling.