SQL: changing data and referential integrity

INSERT, UPDATE and DELETE at A level, and how a database refuses orphan records. The robot logs its own moves.

A11.5Databases and big dataA level55 min

At GCSE (F7.4) you added, corrected and removed records with INSERT, UPDATE and DELETE. At A level the same statements work across a database of linked tables, and that raises a new question: what should happen to a robot's runs when the robot is deleted? The answer is referential integrity, and the database can enforce it for you. By the end of the lesson BugBot logs its own moves into the club database.

The club database from the last lesson:

# club.py: the robotics club's database, in third normal form
import sqlite3

TEAMS = [(1, "Hawks"), (2, "Owls"), (3, "Kites")]
ROBOTS = [(1, "Ada", "green", 1), (2, "Bolt", "red", 1), (3, "Cog", "blue", 2), (4, "Dot", "yellow", 2)]
TASKS = [("W", "wall"), ("S", "square"), ("L", "line")]
RUNS = [(1, 1, "W", 42.0, 3.1), (2, 1, "S", 80.0, 9.4), (3, 2, "W", 38.0, 2.7), (4, 1, "W", 45.5, 3.0),
        (5, 3, "S", 76.5, 8.8), (6, 2, "S", 81.0, 10.2), (7, 4, "L", 60.0, 6.5)]

def open_db():
    """A fresh database with the teams, robots, tasks and runs tables."""
    db = sqlite3.connect(":memory:")
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("CREATE TABLE teams (team_id INTEGER PRIMARY KEY, team_name VARCHAR(20) NOT NULL)")
    db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name VARCHAR(20) NOT NULL, colour VARCHAR(10), "
               "team_id INTEGER REFERENCES teams(team_id))")
    db.execute("CREATE TABLE tasks (task_code CHAR(1) PRIMARY KEY, task_name VARCHAR(20) NOT NULL)")
    db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER REFERENCES robots(robot_id), "
               "task_code CHAR(1) REFERENCES tasks(task_code), cm REAL, seconds REAL)")
    db.executemany("INSERT INTO teams VALUES (?, ?)", TEAMS)
    db.executemany("INSERT INTO robots VALUES (?, ?, ?, ?)", ROBOTS)
    db.executemany("INSERT INTO tasks VALUES (?, ?)", TASKS)
    db.executemany("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", RUNS)
    db.commit()
    return db

def show(db, sql, params=()):
    """Run a query and print each record on its own line."""
    for row in db.execute(sql, params):
        print(*row)

INSERT

import club
db = club.open_db()

db.execute("INSERT INTO teams VALUES (4, 'Wrens')")
db.execute("INSERT INTO robots (robot_id, name, team_id) VALUES (5, 'Eve', 4)")
db.execute("INSERT INTO runs (robot_id, task_code, cm, seconds) VALUES (5, 'W', 40.5, 3.4), (5, 'L', 55.0, 5.9)")
club.show(db, "SELECT * FROM robots WHERE robot_id = 5")
club.show(db, "SELECT * FROM runs WHERE robot_id = 5")

Run this in the simulator

With no field list, VALUES must give every field, in the order the table was created.
With a field list, the fields left out get their default, usually null. Eve has no colour.
run_id was left out, and SQLite filled it in with the next number: a field declared INTEGER PRIMARY KEY numbers itself. Other databases call this AUTO_INCREMENT or IDENTITY.
One INSERT can add several records, each in its own brackets.

UPDATE

import club
db = club.open_db()

db.execute("UPDATE runs SET cm = cm + 0.5, seconds = seconds - 0.1 WHERE robot_id = 2")
club.show(db, "SELECT run_id, cm, seconds FROM runs WHERE robot_id = 2")
print("---")
db.execute("UPDATE robots SET colour = 'purple' WHERE team_id = (SELECT team_id FROM teams WHERE team_name = 'Owls')")
club.show(db, "SELECT name, colour FROM robots")

Run this in the simulator

SET can change several fields at once, and a new value can be worked out from the old one. The WHERE picks the records to change, and can use a nested SELECT to look up a key from another table. With no WHERE, every record in the table changes.

DELETE

import club
db = club.open_db()

db.execute("DELETE FROM runs WHERE cm < 45")
club.show(db, "SELECT run_id, cm FROM runs")
print("rows deleted just now:", db.execute("DELETE FROM runs WHERE task_code = 'L'").rowcount)

Run this in the simulator

DELETE FROM removes whole records; to clear one field, UPDATE it to NULL instead. rowcount tells the program how many records a statement changed.

Referential integrity

Referential integrity means that every foreign key value refers to a record that really exists: every run's robot_id matches a robot, and every robot's team_id matches a team. A foreign key that matches nothing is an orphan, and it makes the data meaningless: a run by a robot nobody can find.

Four kinds of change could break it:

Change	Why it breaks integrity
insert a child with a foreign key that matches no parent	a run for robot 9, which does not exist
update a child's foreign key to a value that matches no parent	move a run to robot 9
delete a parent that still has children	delete Ada while her runs remain
change a parent's primary key while children refer to it	renumber Ada from 1 to 10

A DBMS that enforces referential integrity refuses the first two outright. For the last two, the table's designer chooses what happens:

restrict: refuse to delete or change the parent while children refer to it (the default in most DBMSs, written RESTRICT or NO ACTION);
cascade: delete or change the children too (ON DELETE CASCADE, ON UPDATE CASCADE);
set null: keep the children but empty their foreign key (ON DELETE SET NULL).

import sqlite3
import club
db = club.open_db()

try:
    db.execute("INSERT INTO runs (robot_id, task_code, cm, seconds) VALUES (9, 'W', 40.0, 3.0)")
except sqlite3.IntegrityError as e:
    print("insert refused:", e)

try:
    db.execute("DELETE FROM robots WHERE name = 'Ada'")
except sqlite3.IntegrityError as e:
    print("delete refused:", e)

db.execute("DELETE FROM teams WHERE team_name = 'Kites'")
print("Kites deleted: it had no robots")

Run this in the simulator

SQLite only checks foreign keys once PRAGMA foreign_keys = ON has been run on the connection, and the pragma does nothing if a transaction is already open, so run it straight after connecting. club.open_db() does. Most other DBMSs check foreign keys all the time.

Here is the same database with cascading deletes. Deleting a robot takes its runs with it:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT)")
db.execute("""CREATE TABLE runs (run_id INTEGER PRIMARY KEY,
              robot_id INTEGER REFERENCES robots(robot_id) ON DELETE CASCADE, cm REAL)""")
db.executemany("INSERT INTO robots VALUES (?, ?)", [(1, "Ada"), (2, "Bolt")])
db.executemany("INSERT INTO runs (robot_id, cm) VALUES (?, ?)", [(1, 42.0), (2, 38.0), (1, 80.0)])

db.execute("DELETE FROM robots WHERE robot_id = 1")
print(db.execute("SELECT * FROM runs").fetchall())

Run this in the simulator

Cascading is convenient but dangerous: one careless DELETE on a parent can empty several tables. Restrict is the safer default.

The robot logs its own runs

The robot drives, measures each leg from position() and the clock, and inserts a record with ? placeholders. The values travel separately from the SQL, so text a user typed can never change what the statement does (SQL injection, F11.8).

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import club
db = club.open_db()
db.execute("INSERT INTO tasks VALUES ('D', 'drive')")

for drive in [forward, right, backward, left]:
    x0, y0 = position()
    start = clock()
    drive(40, distance=15)
    x1, y1 = position()
    cm = round(((x1 - x0) ** 2 + (y1 - y0) ** 2) ** 0.5, 1)
    seconds = round(clock() - start, 1)
    db.execute("INSERT INTO runs (robot_id, task_code, cm, seconds) VALUES (?, ?, ?, ?)", (1, "D", cm, seconds))

club.show(db, """SELECT runs.run_id, robots.name, tasks.task_name, runs.cm, runs.seconds
                 FROM runs
                 JOIN robots ON runs.robot_id = robots.robot_id
                 JOIN tasks ON runs.task_code = tasks.task_code
                 WHERE tasks.task_code = 'D'""")

Run this in the simulator

In the exam

Questions ask you to write INSERT, UPDATE and DELETE statements for given tables, to define referential integrity, and to explain what a DBMS does when a change would break it, with an example from the scenario. Always include the WHERE in an update or delete, and say which table the change is made to.

AQA A level 4.10.4 4.10.2

AQA asks you to use SQL to update, insert and delete data in multiple tables of a relational database (4.10.4). A foreign key is only meaningful if it matches a primary key in the other table (4.10.2).

UPDATE Robot SET Colour = 'purple' WHERE RobotID = 3
DELETE FROM Run WHERE Cm < 45
INSERT INTO Run (RobotID, TaskCode, Cm, Seconds) VALUES (1, 'D', 15.0, 2.4)

OCR A level 1.3.2

OCR lists referential integrity in its databases topic: be ready to define it and explain how a DBMS keeps it, for example by refusing to delete a record whose primary key is used as a foreign key elsewhere. OCR's SQL includes INSERT and DELETE.

Eduqas A level 2.5

Eduqas asks about keeping a database consistent. Explain referential integrity with an example of each way it could be broken.

Task: log the legs, keep the links

The starter builds two tables, robots and runs. runs.robot_id refers to robots(robot_id) with ON DELETE CASCADE. Robot 1 is Ada (the robot on the mat) and robot 2 is Bolt, who already has run 1. legs is a list of (move, cm), where move is "forward", "right" or "backward" and cm is a whole number of centimetres.

Switch foreign key checking on.
For each leg in order, drive it at speed 40, measure how far the robot really moved from position() before and after (the straight-line distance, rounded to a whole number), and INSERT it as a run for robot 1 with a field list and ? placeholders, letting the database number it.
Try to insert a run for robot 9. When the database refuses with sqlite3.IntegrityError, print refused: robot 9 does not exist.
UPDATE the runs whose move is right so their move is strafe.
DELETE Bolt from robots.
With a query that joins runs to robots, print every run in run order as run <run_id> <name> <move> <cm>, then print runs: <n>, the number of records left in runs.

Five lines in all, starting with the refusal.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
db.execute("""CREATE TABLE runs (run_id INTEGER PRIMARY KEY,
              robot_id INTEGER NOT NULL REFERENCES robots(robot_id) ON DELETE CASCADE,
              move TEXT, cm INTEGER)""")
db.executemany("INSERT INTO robots VALUES (?, ?)", [(1, "Ada"), (2, "Bolt")])
db.execute("INSERT INTO runs VALUES (1, 2, 'forward', 30)")
db.commit()

legs = [("forward", 20), ("right", 15), ("backward", 10)]

Challenges

Change the runs table so deleting a robot sets its runs' robot_id to null instead. What must you remove from the table definition first?
Using club.py, give every robot in Hawks 1 cm extra on its wall runs with one UPDATE and a nested SELECT.
Why do most designers choose restrict rather than cascade for a table of exam results?