Chapter 10 · Changing Data Safely
INSERT and Multi-Row Inserts
INSERT creates new rows. Safe insertion starts with an explicit target-column contract, continues with constraint-aware input, and ends by verifying exactly what the database accepted.
Learning outcomes
INSERT maps an input row set into a target table. The target schema validates every row through data types, defaults, uniqueness, checks, and foreign keys.
Write single-row and multi-row INSERT statements with explicit column lists.
Use DEFAULT VALUES and omit columns intentionally.
Insert rows produced by a SELECT query.
Capture generated identifiers and accepted values with RETURNING.
Explain statement atomicity and constraint failure behavior.
The insertion pipeline
An INSERT succeeds only after each proposed row can be mapped and validated by the target table.
Always name target columns
INSERT INTO customer ( email, full_name, status, loyalty_points)VALUES ( 'sara@example.com', 'Sara Kim', 'active', 25);An explicit list documents the contract and survives many schema changes. Omitting the list couples the statement to the physical column order and forces values for columns that could have defaults.
Insert multiple rows as one statement
INSERT INTO product ( sku, product_name, unit_price, stock_qty)VALUES ('SQL-INDEX', 'SQL Indexing Lab', 39.00, 10), ('SQL-TXN', 'Transaction Lab', 45.00, 8), ('SQL-DESIGN','Schema Design Kit',55.00, 6)RETURNING product_id, sku, stock_qty;A multi-row statement reduces round trips and gives the database one set-oriented operation. If one row violates a constraint under the default conflict policy, SQLite aborts the statement rather than silently accepting a partial batch.
Defaults are part of the schema contract
INSERT INTO customer (email, full_name)VALUES ('maya@example.com', 'Maya Patel')RETURNING customer_id, status, loyalty_points, updated_at;The omitted columns receive their declared defaults. Use DEFAULT VALUES only when every required column has a usable default or permits NULL.
CREATE TEMP TABLE processing_run ( run_id INTEGER PRIMARY KEY, state TEXT NOT NULL DEFAULT 'queued', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO processing_run DEFAULT VALUESRETURNING run_id, state, created_at;INSERT ... SELECT moves a query result
INSERT INTO customer ( email, full_name, status)SELECT s.email, TRIM(s.full_name), s.statusFROM customer_stage AS sWHERE NOT EXISTS ( SELECT 1 FROM customer AS c WHERE c.email = s.email)ORDER BY s.emailRETURNING customer_id, email, status;The SELECT controls which rows qualify and how values are transformed. The target still enforces its own constraints; staging validation does not replace destination validation.
Generated values and RETURNING
Generated key
Read the database-assigned key instead of guessing it.
Accepted value
Observe timestamps and defaults after the database applies them.
Affected rows
Return the rows actually inserted to the application or operator.
Do not assume
SQLite does not guarantee a useful RETURNING row order unless the application imposes its own handling.
INSERT INTO sales_order ( external_ref, customer_id, status, total_amount)VALUES ('WEB-105', 1, 'draft', 0)RETURNING order_id, external_ref, status, ordered_at;Constraint-aware batch design
| Risk | Example | Safer design |
|---|---|---|
| Duplicate identity | Repeated email or SKU | Define a UNIQUE key and choose an explicit conflict policy. |
| Invalid reference | Unknown customer_id | Load parents first or reject the child row. |
| Invalid domain | Negative stock or unsupported status | Keep CHECK constraints and validate before loading. |
| Partial application | Application sends rows one by one | Use one statement or wrap related statements in a transaction. |
| Retry duplication | Network retry repeats an event | Carry an external unique request or entity key. |
Reusable Chapter 10 practice database
Run this setup once in a disposable SQLite database. Every lesson uses the same constrained tables so that write behavior, references, conflicts, and verification can be compared consistently.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS change_request;DROP TABLE IF EXISTS order_archive;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS product_feed;DROP TABLE IF EXISTS customer_stage;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')), loyalty_points INTEGER NOT NULL DEFAULT 0 CHECK (loyalty_points >= 0), deleted_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0), discontinued_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL UNIQUE, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'paid', 'cancelled', 'refunded')), total_amount REAL NOT NULL DEFAULT 0 CHECK (total_amount >= 0), ordered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE, line_no INTEGER NOT NULL, product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price REAL NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_no)) STRICT;CREATE TABLE customer_stage ( email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'closed'))) STRICT;CREATE TABLE product_feed ( sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0), source_time TEXT NOT NULL) STRICT;CREATE TABLE order_archive ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL, customer_id INTEGER NOT NULL, status TEXT NOT NULL, total_amount REAL NOT NULL, ordered_at TEXT NOT NULL, archived_at TEXT NOT NULL) STRICT;CREATE TABLE change_request ( request_key TEXT PRIMARY KEY, operation TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO customer (customer_id, email, full_name, status, loyalty_points, updated_at)VALUES (1, 'nadia@example.com', 'Nadia Rahimi', 'active', 120, '2026-08-01 09:00:00'), (2, 'omar@example.com', 'Omar Haddad', 'active', 80, '2026-08-01 09:00:00'), (3, 'lina@example.com', 'Lina Chen', 'paused', 40, '2026-08-01 09:00:00'), (4, 'ava@example.com', 'Ava Morgan', 'active', 0, '2026-08-01 09:00:00'), (5, 'noah@example.com', 'Noah Silva', 'closed', 15, '2026-08-01 09:00:00');INSERT INTO product (product_id, sku, product_name, unit_price, stock_qty, updated_at)VALUES (10, 'SQL-FOUND', 'Database Foundations', 49.00, 35, '2026-08-01 09:00:00'), (11, 'SQL-QUERY', 'SQL Query Practice', 69.00, 18, '2026-08-01 09:00:00'), (12, 'SQL-CARD', 'SQL Reference Card', 15.00, 80, '2026-08-01 09:00:00'), (13, 'SQL-LAB', 'SQLite Lab Bundle', 29.00, 22, '2026-08-01 09:00:00'), (14, 'DQ-WORK', 'Data Quality Workbook', 24.50, 0, '2026-08-01 09:00:00');INSERT INTO sales_order (order_id, external_ref, customer_id, status, total_amount, ordered_at)VALUES (100, 'WEB-100', 1, 'paid', 79.00, '2026-07-01 10:00:00'), (101, 'WEB-101', 2, 'draft', 0.00, '2026-07-10 11:00:00'), (102, 'WEB-102', 3, 'cancelled', 29.00, '2025-12-01 12:00:00'), (103, 'WEB-103', 4, 'paid', 69.00, '2026-08-01 13:00:00'), (104, 'WEB-104', 5, 'refunded', 15.00, '2025-11-15 14:00:00');INSERT INTO order_item VALUES (100, 1, 10, 1, 49.00), (100, 2, 13, 1, 30.00), (102, 1, 13, 1, 29.00), (103, 1, 11, 1, 69.00), (104, 1, 12, 1, 15.00);INSERT INTO customer_stage VALUES ('marta@example.com', 'Marta Costa', 'active'), ('reza@example.com', 'Reza Nouri', 'active'), ('lina@example.com', 'Lina Chen', 'active');INSERT INTO product_feed VALUES ('SQL-FOUND', 'Database Foundations', 52.00, 31, '2026-08-05 06:00:00'), ('SQL-CARD', 'SQL Reference Card', 15.00, 75, '2026-08-05 06:00:00'), ('SQL-OPS', 'Database Operations', 89.00, 12, '2026-08-05 06:00:00');The setup drops tables. Never execute training reset scripts against a production database.
Practice lab
- Insert one customer while accepting schema defaults.
- Insert three products in one statement and return their generated IDs.
- Load only new staging customers with INSERT ... SELECT.
- Attempt a duplicate email and observe the constraint failure.
- Explain which unit should be atomic: one row, one batch, or a parent plus its children.
BEGIN;INSERT INTO sales_order ( external_ref, customer_id, status, total_amount)VALUES ('WEB-106', 2, 'paid', 84.00);INSERT INTO order_item ( order_id, line_no, product_id, quantity, unit_price)VALUES ((SELECT order_id FROM sales_order WHERE external_ref = 'WEB-106'), 1, 11, 1, 69.00), ((SELECT order_id FROM sales_order WHERE external_ref = 'WEB-106'), 2, 12, 1, 15.00);COMMIT;Checkpoint
Design safe inserts
- Why should target columns be named?
- What advantage does multi-row VALUES provide?
- Does staging validation replace target constraints?
- Why should an application read generated keys from the database?
- What makes a retried insert idempotent?
Review the answers
Column lists make the mapping explicit. Multi-row VALUES is set-oriented and reduces round trips. The target must still enforce validity. Generated keys and defaults are database decisions. A stable unique business or request key makes retries recognizable.
Summary and references
- INSERT consumes a row set and maps it to named target columns.
- Defaults and generated values should be observed, not guessed.
- INSERT ... SELECT is the set-based bridge from staging to destination.
- Constraints and transactions define the real acceptance boundary.