INSERT adds rows, and the only hard part is doing it in a way that survives contact with reality: naming your columns, supplying values that match them, and letting the schema refuse anything that would corrupt the data. An INSERT that fails loudly is a good INSERT — the danger is the one that succeeds half-intended.
How does INSERT add rows correctly?
The ticketing database in this module has four tables: venues, events, ticket_holders and tickets. Each event belongs to a venue, each ticket belongs to an event and a holder, and every ticket carries a seat code and a price.
Four tables hold the ticketing data, and they lean on each other in a chain: a venue hosts events, an event sells tickets, and a ticket belongs to a member of the public. The keys that link them are declared here and enforced from module 09 onwards:
CREATE TABLE venues (
venue_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT,
capacity INTEGER
);
CREATE TABLE events (
event_id INTEGER PRIMARY KEY,
venue_id INTEGER REFERENCES venues(venue_id),
name TEXT NOT NULL,
event_date TEXT,
ticket_price REAL
);
CREATE TABLE ticket_holders (
holder_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL,
city TEXT,
signed_up_on TEXT
);
CREATE TABLE tickets (
ticket_id INTEGER PRIMARY KEY,
event_id INTEGER REFERENCES events(event_id),
holder_id INTEGER REFERENCES ticket_holders(holder_id),
seat_code TEXT,
price_paid REAL,
sold_at TEXT,
status TEXT DEFAULT 'sold'
);
The module's sixteen tickets, six members, six events and three venues load like this:
INSERT INTO venues (venue_id, name, city, capacity) VALUES
(1, 'Riverside Hall', 'Bristol', 420),
(2, 'Kingsway Theatre', 'Cardiff', 610),
(3, 'The Old Mill', 'Leeds', 180);
INSERT INTO events (event_id, venue_id, name, event_date, ticket_price) VALUES
(1, 1, 'Winter Sessions', '2026-12-04', 45.00),
(2, 2, 'Stand-up Night', '2026-11-12', 22.00),
(3, 1, 'Poetry in the Park', '2026-10-30', 12.00),
(4, 2, 'Comedy Cellar', '2026-11-19', 18.00),
(5, 3, 'Regional Folk Festival', '2026-12-18', 30.00),
(6, 1, 'Late Night Jazz', '2026-12-31', 16.50);
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on) VALUES
(1, '[email protected]', 'Imogen Fair', 'Bristol', '2024-03-12'),
(2, '[email protected]', 'Callum Reid', 'Cardiff', '2023-06-21'),
(3, '[email protected]', 'Harriet Vance', 'Bristol', '2025-01-19'),
(4, '[email protected]', 'Lukas Meyer', 'Glasgow', '2022-09-30'),
(5, '[email protected]', 'Vera Ostrowski', 'Leeds', '2024-02-14'),
(6, '[email protected]', 'Nadia Haddad', 'Bristol', '2025-04-02');
INSERT INTO tickets (ticket_id, event_id, holder_id, seat_code, price_paid, sold_at, status) VALUES
(1, 1, 2, 'A-01', 45.00, '2026-09-14', 'sold'),
(2, 1, 5, 'A-02', 45.00, '2026-09-14', 'sold'),
(3, 1, 2, 'B-03', 45.00, '2026-09-21', 'sold'),
(4, 2, 4, 'C-04', 22.00, '2026-09-28', 'sold'),
(5, 2, 1, 'C-05', 22.00, '2026-10-01', 'sold'),
(6, 3, 3, 'D-06', 12.00, '2026-10-05', 'sold'),
(7, 3, 6, 'D-07', 12.00, '2026-10-05', 'sold'),
(8, 4, 2, 'E-05', 18.00, '2026-10-09', 'sold'),
(9, 4, 5, 'E-06', 18.00, '2026-10-09', 'sold'),
(10, 4, 5, 'E-07', 18.00, '2026-10-12', 'sold'),
(11, 5, 1, 'F-11', 30.00, '2026-10-15', 'sold'),
(12, 5, 4, 'F-12', 30.00, '2026-10-15', 'sold'),
(13, 6, 2, 'G-13', 16.50, '2026-10-19', 'sold'),
(14, 6, 5, 'G-14', 16.50, '2026-10-19', 'sold'),
(15, 2, 5, 'C-15', 22.00, '2026-09-05', 'refunded'),
(16, 6, 6, 'G-16', 16.50, '2026-10-22', 'sold');
Why should you always name the columns?
The safest form of INSERT lists the columns explicitly and supplies the values in the same order. Sixteen tickets exist before we start:
SELECT COUNT(*) AS tickets_before FROM tickets;
tickets_before
16
Now one ticket is sold, with the column list spelled out:
BEGIN;
INSERT INTO tickets (ticket_id, event_id, holder_id, seat_code, price_paid, sold_at)
VALUES (17, 1, 4, 'B-07', 45.00, '2026-10-28');
SELECT ticket_id, event_id, holder_id, seat_code, price_paid, sold_at, status
FROM tickets WHERE ticket_id = 17;
ticket_id event_id holder_id seat_code price_paid sold_at status
--------- -------- --------- --------- ---------- ---------- ----
17 1 4 B-07 45.0 2026-10-28 sold
List the columns and the statement keeps working when the table gains a column, when the column order changes, and when a colleague reorders the schema without telling you. The alternative — INSERT INTO tickets VALUES (...) — depends on the physical column order and breaks silently on the next schema change, which is exactly the kind of breakage you find in production at the worst possible moment.
What about inserting several rows at once?
A single statement can carry many value tuples, and it should: one round trip beats three, and the statement either succeeds as a unit or fails as a unit.
INSERT INTO tickets (ticket_id, event_id, holder_id, seat_code, price_paid, sold_at) VALUES
(18, 4, 6, 'E-08', 18.00, '2026-10-28'),
(19, 4, 4, 'E-09', 18.00, '2026-10-28'),
(20, 2, 2, 'C-24', 22.00, '2026-10-28');
SELECT COUNT(*) AS tickets_after FROM tickets;
tickets_after
-------------
20
Three rows in, count goes from seventeen to twenty. Note that the seat codes are real seat references and the prices match the events: the Comedy Cellar sells at 18 pounds and Stand-up Night at 22. An INSERT that gets those two out of step is a data error no constraint will catch unless you build one.
What happens to a column you leave out?
Omit it and it takes its default value, if the schema declares one. The status column is declared DEFAULT 'sold', so a ticket inserted without a status comes out marked as sold:
INSERT INTO tickets (ticket_id, event_id, holder_id, seat_code, price_paid, sold_at)
VALUES (21, 6, 1, 'G-11', 16.50, '2026-10-28');
SELECT ticket_id, seat_code, status FROM tickets WHERE ticket_id = 21;
ticket_id seat_code status
--------- --------- ------
21 G-11 sold
With no default declared, an omitted column becomes NULL — which is a very different thing, and is the source of the blank cells lesson 15 spent time on. If a column is genuinely mandatory, declare it NOT NULL and let the database refuse the row rather than storing a hole in your data.
How do you insert rows that come from a query?
INSERT ... SELECT copies a result set into a table, and the SELECT can be any query at all. Here three Bristol-based ticket holders are each given a seat at the Late Night Jazz event, with the new identifiers derived from the source rows:
INSERT INTO tickets (ticket_id, event_id, holder_id, seat_code, price_paid, sold_at)
SELECT 100 + holder_id, 6, holder_id, 'H-' || holder_id, 16.50, '2026-10-28'
FROM ticket_holders WHERE city = 'Bristol';
SELECT ticket_id, holder_id, seat_code FROM tickets WHERE ticket_id > 100;
ROLLBACK;
ticket_id holder_id seat_code
--------- --------- ---------
101 1 H-1
103 3 H-3
106 6 H-6
Three rows created without naming a single holder explicitly. The ticket identifiers are computed from the holder identifiers, which is a common pattern and a common trap: if the source query returns the same key twice, the INSERT fails on the primary key. When copying rows, decide whether you want the source's identifiers or fresh ones, and be explicit about it.
The ROLLBACK at the end of that statement puts the demonstration seats back on the shelf: the four sales made in this lesson are undone, so every exercise in this module starts from the same sixteen tickets. Transactions are the subject of lesson 36; for now the transaction is simply the tidiest way to run a demonstration without leaving litter behind.
Which mistakes does the database refuse?
Most of them, and that refusal is the whole point of putting data in a database rather than a spreadsheet. Reusing an existing venue identifier is rejected:
INSERT INTO venues (venue_id, name, city, capacity)
VALUES (1, 'Riverside Hall Annexe', 'Bristol', 90);
UNIQUE constraint failed: venues.venue_id
Leaving a required column empty is rejected:
INSERT INTO venues (venue_id, name, city, capacity)
VALUES (9, NULL, 'Cardiff', 120);
NOT NULL constraint failed: venues.name
And pointing a ticket at an event that does not exist is rejected once foreign keys are enforced:
PRAGMA foreign_keys = ON;
INSERT INTO tickets (ticket_id, event_id, holder_id, seat_code, price_paid, sold_at)
VALUES (30, 999, 1, 'Z-01', 10.00, '2026-10-28');
FOREIGN KEY constraint failed
Three refusals, three different guarantees. In SQLite, foreign key checking is off by default and must be switched on with PRAGMA foreign_keys = ON — which is itself a lesson about how much protection you actually have. The four constraints and the referential actions behind them are the subject of modules 08 and 09.
Quick reference
- Always name the columns in an INSERT; never rely on the physical column order.
- One multi-row INSERT beats several single-row statements, and it behaves as a single unit.
- An omitted column takes its DEFAULT, or NULL when no default is declared.
- INSERT ... SELECT derives rows from a query, so duplicate source keys can break the insert.
- NOT NULL, UNIQUE and foreign key constraints refuse bad rows for you — provided they are switched on.
Insert drill: sell three more seats at the Winter Sessions for holders who already hold a ticket at that event — write the INSERT that copies from the tickets table rather than retyping the values, and make sure the generated ticket identifiers cannot collide with the ones already used.