SQL INSERT, UPDATE, DELETE, Constraints and Primary and Foreign Keys

What stops a tired clerk from entering a seed packet with a price of zero, or from deleting a plant species that thirty packets still refer to? In a well-designed database the answer is not a code review or a warning in the user interface. The table itself refuses. This lesson covers the three statements that change data and the constraints that decide which changes are allowed.

Start with the rules, then write the rows

The schema below describes a small nursery. Each column carries its own rules, and the table-level promises matter just as much as the types.

CREATE TABLE species (
  species_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL UNIQUE,
  sun_need   TEXT NOT NULL CHECK (sun_need IN ('full', 'partial', 'shade'))
);

CREATE TABLE seed_packets (
  sku        TEXT    PRIMARY KEY,
  species_id INTEGER NOT NULL REFERENCES species(species_id),
  price_eur  REAL    NOT NULL CHECK (price_eur > 0),
  in_stock   INTEGER NOT NULL DEFAULT 0 CHECK (in_stock >= 0)
);

A primary key is the row's identity: sku for packets, species_id for species. A foreign key such as seed_packets.species_id is a promise that the referenced species exists. Keys are either natural (a real-world code like sku) or surrogate (a meaningless number the database assigns, like species_id). Natural keys are readable but break when the business renames them; surrogate keys stay stable.

INSERT: adding rows

List the target columns explicitly. Without the column list, the values must match the table's exact column order, and the statement breaks the day someone adds a column. Columns you omit receive their DEFAULT, or NULL if they have none (and fail if they are NOT NULL). Insert parents before children, since a packet cannot reference a species that does not exist yet.

INSERT INTO species (name, sun_need) VALUES
  ('Sweet Pea',   'full'),
  ('Foxglove',    'partial'),
  ('Wild Garlic', 'shade');

INSERT INTO seed_packets (sku, species_id, price_eur) VALUES
  ('SP-001', 1, 2.40),
  ('FG-002', 2, 3.10);

INSERT INTO seed_packets (sku, species_id, price_eur, in_stock)
VALUES ('WG-003', 3, 2.75, 40);

SELECT * FROM seed_packets;

sku    | species_id | price_eur | in_stock
-------+------------+-----------+---------
SP-001 |          1 |       2.4 |        0
FG-002 |          2 |       3.1 |        0
WG-003 |          3 |      2.75 |       40

Two packets omit in_stock and receive the default 0, while the third supplies 40. Now see what the constraints do when a row breaks a rule. Each statement below is rejected, and the table stays unchanged.

INSERT INTO seed_packets (sku, species_id, price_eur) VALUES ('XX-009', 1, 0);

INSERT INTO seed_packets (sku, species_id, price_eur) VALUES ('XX-010', 99, 1.50);

INSERT INTO seed_packets (sku, species_id, price_eur) VALUES ('SP-001', 1, 2.40);

INSERT INTO seed_packets (sku, price_eur) VALUES ('XX-011', 1.50);

INSERT INTO species (name, sun_need) VALUES ('Foxglove', 'shade');

Error: CHECK constraint failed: price_eur > 0

Error: FOREIGN KEY constraint failed

Error: UNIQUE constraint failed: seed_packets.sku

Error: NOT NULL constraint failed: seed_packets.species_id

Error: UNIQUE constraint failed: species.name

In order: a price of zero fails the CHECK, a species that does not exist fails the foreign key, a duplicate sku fails the primary key, a missing species_id fails NOT NULL, and a second 'Foxglove' fails the UNIQUE constraint on the species name. Because the database enforces these rules for every client, an import script, an admin tool and a bug in the app all get the same protection. Application-level validation is still useful for friendly messages, but it is not a substitute.

UPDATE: changing rows that already exist

UPDATE table SET column = expression WHERE condition rewrites every row the condition matches. The expression can refer to the row's own current values, which is the right way to adjust counters and prices (in_stock = in_stock + 25) instead of reading a value into your application and writing it back.

UPDATE seed_packets
SET in_stock = in_stock + 25
WHERE sku IN ('SP-001', 'FG-002');

UPDATE seed_packets
SET price_eur = ROUND(price_eur * 1.10, 2)
WHERE species_id = (SELECT species_id FROM species WHERE name = 'Sweet Pea');

SELECT * FROM seed_packets;

sku    | species_id | price_eur | in_stock
-------+------------+-----------+---------
SP-001 |          1 |      2.64 |       25
FG-002 |          2 |       3.1 |       25
WG-003 |          3 |      2.75 |       40

Both updates did what they say: two packets received 25 units, and the Sweet Pea price rose by ten percent, rounded. The constraints still apply to updates. An attempt to sell more than we hold would push in_stock below zero, and the database stops it.

UPDATE seed_packets SET in_stock = in_stock - 30 WHERE sku = 'SP-001';

Error: CHECK constraint failed: in_stock >= 0

The missing WHERE and how to rehearse

The most expensive typo in SQL is leaving off the WHERE. UPDATE and DELETE without one apply to every row, and the database will not ask whether you are sure. Two habits protect you: first run the same condition as a SELECT (or SELECT COUNT(*)) to see exactly which rows would change, and run risky changes inside a transaction so that you can look at the result and then undo it.

BEGIN;
SELECT COUNT(*) AS rows_to_change FROM seed_packets WHERE in_stock < 30;
UPDATE seed_packets SET price_eur = 0.01;
SELECT sku, price_eur FROM seed_packets;
ROLLBACK;
SELECT sku, price_eur FROM seed_packets;

rows_to_change
--------------
             2

sku    | price_eur
-------+----------
SP-001 |      0.01
FG-002 |      0.01
WG-003 |      0.01

sku    | price_eur
-------+----------
SP-001 |      2.64
FG-002 |       3.1
WG-003 |      2.75

The preview count says two packets are low on stock, which is the kind of number you compare against what you expected. The deliberate mistake that follows, an update with no WHERE, set every price to 0.01 inside the transaction, as the second table proves, and the ROLLBACK restored all three original prices in the third. In a real session you would run COMMIT instead of ROLLBACK once the result looked right. A transaction starts with BEGIN in SQLite, and with BEGIN or START TRANSACTION in PostgreSQL and MySQL.

DELETE: removing rows without orphans

Foreign keys protect the other direction too: you cannot delete a parent while children still point at it.

DELETE FROM species WHERE name = 'Wild Garlic';

Error: FOREIGN KEY constraint failed

The packet WG-003 still references that species, so the delete is refused. You have three choices: delete the children first, as below, declare the relationship ON DELETE CASCADE so children disappear with the parent (powerful and dangerous), or reject deletes forever and mark rows inactive instead, a pattern covered in Advanced SQL.

DELETE FROM seed_packets WHERE sku = 'WG-003';
DELETE FROM species WHERE name = 'Wild Garlic';

SELECT * FROM species;

species_id | name      | sun_need
-----------+-----------+---------
         1 | Sweet Pea | full
         2 | Foxglove  | partial

Common mistakes

• UPDATE or DELETE with no WHERE. Symptom: "3 rows affected" when you expected one, and a table full of identical values. Preview with SELECT, use a transaction, and keep backups.
• INSERT without a column list. Symptom: table seed_packets has 4 columns but 3 values were supplied or, worse, values landing in the wrong columns after a schema change.
• Inserting children before parents. Error: FOREIGN KEY constraint failed. Insert in dependency order.
• Relying on the application alone for rules. A second client or a manual fix bypasses it. Put NOT NULL, UNIQUE, CHECK and foreign keys in the schema.
• Assuming SQLite enforces foreign keys by default. If it does not (no PRAGMA), orphaned rows appear and the first sign is a join that silently drops them.

How this shows up in real projects and interviews

In real systems you rarely type these statements by hand. An ORM or a migration tool generates them, and the lasting value is the schema design: which columns are NOT NULL, which pairs must be UNIQUE, how rows reference each other. Interviewers ask about primary versus foreign keys, about what happens when you delete a parent row, and whether you would trust application code alone to keep data valid. A confident answer describes the constraints as the last line of defence that every writer shares. When you are ready for concurrency, safe upserts and soft deletes, continue to the Advanced SQL course.