CS50 SQL — Lecture 3 Notes: INSERT, UPDATE, DELETE, Imports, Triggers & Soft Deletes

Why this lecture matters

Reading data is only half the story. Real systems create, change, and remove data all the time—museum accessions, user accounts, orders, payments, logs. This lecture moves from schema design (Lecture 2) to CRUD operations you’ll use daily:

• INSERT: add rows
• UPDATE: fix typos, change relationships, clean data
• DELETE: remove rows (or soft delete them)
• Bulk imports: load CSVs efficiently
• Foreign key integrity: safe deletes
• Triggers: run automated logic on writes

We’ll anchor examples in the Museum of Fine Arts (MFA) scenario.

---

Setup: a simple MFA schema

-- schema.sql
CREATE TABLE collections (
  id               INTEGER PRIMARY KEY,
  title            TEXT    NOT NULL,
  accession_number TEXT    NOT NULL UNIQUE,
  acquired         NUMERIC       -- YYYY-MM-DD (can be NULL if unknown)
);

-- Optional tables used later:
CREATE TABLE artists (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE created (
  artist_id    INTEGER NOT NULL,
  collection_id INTEGER NOT NULL,
  FOREIGN KEY (artist_id)    REFERENCES artists(id),
  FOREIGN KEY (collection_id) REFERENCES collections(id),
  PRIMARY KEY (artist_id, collection_id)
);

Tip: In SQLite, NUMERIC affinity works well for YYYY-MM-DD strings and lets you compare with <, <=, >, >=.

Load it:

sqlite3 mfa.db
.read schema.sql
.schema

---

INSERT — creating rows

Basic pattern

INSERT INTO collections (id, title, accession_number, acquired)
VALUES (1, 'Profusion of Flowers', '56.257', '1956-04-12');

Let SQLite handle the primary key

INSERT INTO collections (title, accession_number, acquired)
VALUES ('Spring Outing', '14.76', '1914-01-08');

-- SQLite auto-assigns id as (max(id) + 1) by default.

Constraints in action

• NOT NULL prevents NULL inserts.
• UNIQUE prevents duplicate accession_number.

-- Violates UNIQUE if 14.76 already exists:
INSERT INTO collections (title, accession_number, acquired)
VALUES ('Spring Outing', '14.76', '1914-01-08');  -- Runtime error

Multi-row insert (faster + fewer roundtrips)

INSERT INTO collections (title, accession_number, acquired)
VALUES
  ('Imaginative Landscape', '56.496', NULL),
  ('Peonies and Butterfly', '06.1899', '1906-01-01');

Performance note: A single multi-row INSERT is typically faster than many one-row inserts.

---

Importing CSVs

Quick import into an existing table

Your CSV has the header row and columns matching collections:

id,title,accession_number,acquired
1,Profusion of Flowers,56.257,1956-04-12
2,Farmers Working at Dawn,11.6152,1911-08-03
...

sqlite3 mfa.db
.import --csv --skip 1 mfa.csv collections

• --csv tells SQLite to parse commas correctly.
• --skip 1 ignores the header row because schema already exists.

Import when CSV has no id (let DB generate keys)

1) Import into a temporary table auto-created from headers:

.import --csv mfa.csv temp  -- do NOT --skip 1

2) Move data into the real table, letting id auto-generate:

INSERT INTO collections (title, accession_number, acquired)
SELECT title, accession_number, acquired
FROM temp;

3) Clean up:

DROP TABLE temp;

Gotcha: CSV blanks import as empty strings, not NULL. You may need a cleanup UPDATE to convert '' to NULL if you rely on IS NULL logic.

---

DELETE — removing rows safely

Basic delete with a filter

DELETE FROM collections
WHERE title = 'Spring Outing';

Delete by date (numeric comparison on YYYY-MM-DD)

DELETE FROM collections
WHERE acquired < '1909-01-01';

Foreign keys & safe deletes

If another table references the row you’re deleting, SQLite enforces integrity (when foreign keys are enabled) and raises an error unless you:

• Delete referencing rows first, or
• Configure ON DELETE behavior.

CREATE TABLE created (
  artist_id     INTEGER NOT NULL,
  collection_id INTEGER NOT NULL,
  FOREIGN KEY (artist_id)     REFERENCES artists(id)     ON DELETE CASCADE,
  FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
  PRIMARY KEY (artist_id, collection_id)
);

Options you can place after REFERENCES ...:

• ON DELETE RESTRICT — block the delete (default-like behavior).
• ON DELETE NO ACTION — similar to restrict in SQLite.
• ON DELETE SET NULL — null out the foreign key in child rows.
• ON DELETE SET DEFAULT — set to column’s default value.
• ON DELETE CASCADE — delete child rows automatically.

Example workflow without CASCADE (two-step):

-- 1) Remove the relationship in the junction table
DELETE FROM created
WHERE artist_id = (SELECT id FROM artists WHERE name = 'Unidentified artist');

-- 2) Now delete the artist
DELETE FROM artists WHERE name = 'Unidentified artist';

With CASCADE, step 1 happens automatically when you run step 2.

Ensure foreign keys are enforced: PRAGMA foreign_keys = ON; (SQLite).

---

UPDATE — changing data (fixes, relationships, cleanup)

Basic pattern

UPDATE collections
SET title = 'Spring Outing'
WHERE accession_number = '14.76';

Use subqueries to set values

Reassign the artist who created Farmers Working at Dawn from “Unidentified artist” to “Li Yin” in the junction table:

UPDATE created
SET artist_id = (SELECT id FROM artists WHERE name = 'Li Yin')
WHERE collection_id = (
  SELECT id FROM collections WHERE title = 'Farmers Working at Dawn'
);

Data cleaning patterns

• Trim leading/trailing spaces:

  UPDATE votes SET title = TRIM(title);

• Normalize case (for grouping):

  UPDATE votes SET title = UPPER(title);   -- or LOWER(title)

• Fix common typos by pattern:

  UPDATE votes
  SET title = 'FARMERS WORKING AT DAWN'
  WHERE title LIKE 'FA%';   -- use with care; too broad for big datasets

• Manual corrections for stubborn cases:

  UPDATE votes
  SET title = 'IMAGINATIVE LANDSCAPE'
  WHERE title = 'IMAGINTIVE LANDSCAPE';

• Aggregate after cleanup:

  SELECT title, COUNT(*) AS votes
  FROM votes
  GROUP BY title
  ORDER BY votes DESC;

Rule of thumb: prefer precise WHERE clauses over broad patterns when possible.

---

Triggers — automate reactions to writes

Triggers let the database run SQL automatically when INSERT/UPDATE/DELETE happens on a table.

Syntax

CREATE TRIGGER trigger_name
AFTER INSERT ON some_table   -- or BEFORE, and for UPDATE/DELETE
FOR EACH ROW
BEGIN
  -- statements; you can reference NEW.<col> / OLD.<col>
END;

• NEW.column — values of the row being inserted/updated
• OLD.column — values of the row being deleted/updated

Example 1 — log sales on DELETE from collections

CREATE TABLE transactions (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  action TEXT NOT NULL  -- 'sold' or 'bought'
);

CREATE TRIGGER sell
BEFORE DELETE ON collections
FOR EACH ROW
BEGIN
  INSERT INTO transactions (title, action)
  VALUES (OLD.title, 'sold');
END;

Now:

DELETE FROM collections WHERE title = 'Profusion of Flowers';
SELECT * FROM transactions;  -- shows ('Profusion of Flowers', 'sold')

Example 2 — log purchases on INSERT to collections

CREATE TRIGGER buy
AFTER INSERT ON collections
FOR EACH ROW
BEGIN
  INSERT INTO transactions (title, action)
  VALUES (NEW.title, 'bought');
END;

Now a regular insert also writes a transaction entry.

You can put multiple statements between BEGIN … END; separated by semicolons.

---

Soft deletes — keep the row, mark its status

Instead of deleting rows, keep them and mark as deleted (useful for auditability; be mindful of data privacy obligations).

Alter the table to add a soft-delete flag

ALTER TABLE collections
ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;

“Delete” by updating the flag

UPDATE collections
SET deleted = 1
WHERE title = 'Farmers Working at Dawn';

Query to exclude deleted rows

SELECT * FROM collections WHERE deleted <> 1;

See only deleted rows

SELECT * FROM collections WHERE deleted = 1;

Pros

• Easy to restore.
• Keeps history.

Cons

• Requires all reads to filter properly.
• May conflict with compliance (e.g., GDPR “right to be forgotten”).

Combine with triggers to maintain a separate audit table for full history.

---

Practical tips & gotchas

• Primary keys: Let the DB assign them unless you must preserve an external ID.
• CSV imports:
  • Expect blank strings, not NULL. Clean them.
  • Ensure headers match column names or import to a temp table first.
• Constraints are guardrails: NOT NULL, UNIQUE, CHECK, and foreign keys prevent data corrosion.
• Always use WHERE for UPDATE/DELETE unless you truly want to touch every row.
• Case-insensitive matching: LIKE is case-insensitive in SQLite; GLOB is case-sensitive.
• Foreign keys in SQLite are off by default in some environments — set PRAGMA foreign_keys = ON; (ideally via connection settings in apps).

---

Quick reference (SQLite)

.schema [table]          -- see schema
.mode table              -- pretty-print results
.headers on              -- show headers in query output
.import --csv file.csv t -- import into table t
.timer on                -- see query timing
PRAGMA foreign_keys = ON;

---

Practice prompts

1) Bulk import a CSV of 10k artworks into collections, leaving id to auto-generate. Convert blank acquired values to NULL and list works acquired before 1920.
2) Enforce integrity: add created with ON DELETE CASCADE. Try deleting an artist and verify child rows are removed.
3) Data cleaning: import a votes.csv, normalize titles (trim, case), fix 3 common typos with UPDATE ... WHERE title LIKE ..., then rank winners with GROUP BY.
4) Triggers: write a trigger to insert an audit row into transactions on every UPDATE to collections.title, capturing the old and new titles.

---

Closing thought

Schemas give your database structure. Writes give it life. With careful use of constraints, UPDATE/DELETE filters, CSV pipelines, triggers, and (when appropriate) soft deletes, you can keep that life both clean and auditable.

Happy querying!