The INSERT adds rows, and the hard part is doing it in a way that survives contact with reality. You name your columns, you supply values that match them, and you let the schema reject anything that would corrupt the data. An INSERT that fails loudly is a good INSERT, because the dangerous one is the one that succeeds when you meant it to fail.
This is the first lesson in the Modifying Data module, and the next lesson in this module is about "How Does UPDATE Change Rows Without Collateral Damage?".
How does the INSERT add the rows correctly?
The ticketing database in this module has four tables: venues, events, ticket_holders and tickets. Every event belongs to a venue, every ticket belongs to an event and to a holder, and every ticket carries a seat code and a price.
Those four tables hold the ticketing data, and they lean on each other in a chain. A venue hosts the events, an event sells the tickets, and a ticket belongs to a member of the public. The keys that link them are declared here, and the database enforces them 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 sixteen tickets, the six members, the six events and the 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 the INSERT lists the columns explicitly and supplies the values in the same order. There are sixteen tickets before we start.
SELECT COUNT(*) AS tickets_before FROM tickets;tickets_before
16Now 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 soldBecause you list the columns, 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, so it breaks silently at the next schema change. That is exactly the kind of breakage that you find in production at the worst possible moment.
What about inserting several rows at once?
One statement can carry many value tuples, and it should. A single 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
-------------
20Three rows go in, and the count goes from seventeen to twenty.
Notice that the seat codes are real seat references, and that 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, and no constraint will catch it unless you build one.
What happens to a column that you leave out?
You leave it out and it takes its default value, if the schema declares one.
The column status is declared DEFAULT 'sold', so a ticket that is 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 soldWhen no default is declared, an omitted column becomes a NULL. That is a very different thing, and it is the source of the blank cells that lesson 15 spent its time on.
If a column is genuinely mandatory, you declare it NOT NULL and you let the database refuse the row, instead of storing a hole in your data.
How do you insert the rows that come from a query?
The 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-6Three rows are created without naming a single holder explicitly.
The ticket identifiers are computed from the holder identifiers, and that is as much a common pattern as a common trap. If the source query returns the same key twice, the INSERT fails on the primary key. When you copy rows, you decide whether you want the identifiers of the source or fresh ones, and you say so in the statement.
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 that every exercise in this module starts from the same sixteen tickets. Transactions are the subject of lesson 36. For now, a 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 the data in a database instead of in 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_idLeaving a required column empty is rejected.
INSERT INTO venues (venue_id, name, city, capacity)
VALUES (9, NULL, 'Cardiff', 120);NOT NULL constraint failed: venues.nameAnd pointing a ticket at an event that does not exist is rejected once the 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 failedThere are three refusals here, and each one is a different guarantee.
In SQLite the foreign key checking is off by default, and you switch it on with PRAGMA foreign_keys = ON. That is itself a lesson about how much protection you really have. The four constraints and the referential actions behind them are the subject of modules 08 and 09.
Quick reference
- You always name the columns in an INSERT, and you 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 a NULL when no default is declared.
- The
INSERT ... SELECTderives the rows from a query, so duplicate source keys can break the insert. - The NOT NULL, the UNIQUE and the foreign key constraints refuse the bad rows for you, provided that they are switched on.
Insert drill: you sell three more seats at the Winter Sessions for the holders who already hold a ticket at that event. You write the INSERT that copies from the tickets table instead of retyping the values, and you make sure that the generated ticket identifiers cannot collide with the ones that are already used.