4.10.4 - SQL table definitions and data changes (Python)

Creating tables and constraints

A table definition turns a data model into a named structure with rules for the records it can hold.

CREATE TABLE names the table, lists its fields and declares their types. Conventional SQL types include INTEGER for whole numbers, VARCHAR(n) for variable-length text with a stated maximum length, CHAR(n) for fixed-length text, DECIMAL(p,s) for exact decimal values with precision p and s decimal places, and DATE for a date. For example, DECIMAL(6,2) allows six decimal digits in total, two after the decimal point. Choose types from the meaning of the data: a telephone number is text if leading zeroes or a + matter, even though it contains digits.

Types and their enforcement vary between SQL systems. SQLite uses flexible typing and does not enforce the length in VARCHAR(n) merely from that declaration. Our runnable SQLite examples use TEXT, INTEGER and REAL; they do not demonstrate exact decimal-money storage or every conventional type rule. In written tasks, use the types and assumptions the task specifies.

A workshop system has members and workshops before it records bookings:

CREATE TABLE Member (
    MemberID INTEGER NOT NULL,
    FullName VARCHAR(60) NOT NULL,
    YearGroup INTEGER NOT NULL,
    PRIMARY KEY (MemberID)
);

CREATE TABLE Workshop (
    WorkshopID INTEGER NOT NULL,
    Title VARCHAR(40) NOT NULL,
    Fee DECIMAL(6,2) NOT NULL,
    PRIMARY KEY (WorkshopID),
    CHECK (Fee >= 0)
);

CREATE TABLE Booking (
    BookingID INTEGER NOT NULL,
    MemberID INTEGER NOT NULL,
    WorkshopID INTEGER NOT NULL,
    Attended INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (BookingID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
    FOREIGN KEY (WorkshopID) REFERENCES Workshop(WorkshopID),
    CHECK (Attended IN (0, 1))
);

Each field declaration and table constraint is separated from the next by a comma; no comma is needed before the closing bracket. A primary key identifies a record uniquely and must not represent a missing value. A key can be a single field or a combination, for example PRIMARY KEY (MemberID, WorkshopID) in a design where each member may book each workshop only once. Choosing a composite key imposes that rule; it would be unsuitable if repeat bookings must be distinguishable.

A foreign key refers to a key in another table. The two booking references allow many bookings for one member or workshop without copying their details. NOT NULL requires a value, DEFAULT 0 supplies a value when that field is omitted during insertion, and CHECK restricts the permitted domain. A default does not prohibit other values; a check does not replace a separate non-NULL requirement. A UNIQUE constraint can enforce a candidate key such as an email address. Create the referenced tables before the referencing table when the system requires the referenced definitions to exist.

Constraints enforce the rules you actually declare. A non-negative fee could still be the wrong fee for a particular workshop. Database integrity therefore complements, rather than replaces, application validation and correct input.

Inserting, updating and deleting records

SQL changes stored records directly, so a correct target condition matters as much as a correct new value.

INSERT INTO adds a new record. Explicit field names show how each supplied value maps to a field and let other fields use a default:

INSERT INTO Member (MemberID, FullName, YearGroup)
VALUES (5, 'Eli Park', 12);

INSERT INTO Booking (BookingID, MemberID, WorkshopID)
VALUES (107, 5, 20);

The second statement assumes member 5 and workshop 20 already exist. Attended is omitted, so its default is 0. Text values need quotes; numbers do not. Supplying a field list avoids relying on every field's physical order. If an omitted field is required and has no suitable default, the database rejects the insertion.

UPDATE changes fields of existing records. SET gives the new assignment and WHERE identifies the records to change:

UPDATE Workshop
SET Fee = Fee + 1.00
WHERE WorkshopID = 20;

Only workshop 20 changes. The expression uses each qualifying record's old fee, rather than assigning a fixed fee of 1.00. To change several fields, use one SET list, for example SET FullName = 'Eli Stone', YearGroup = 13. There is no SELECT field list in an UPDATE statement. Omitting WHERE updates every record; it does not mean “update the current record”.

DELETE FROM removes whole records:

DELETE FROM Booking
WHERE BookingID = 107;

This removes the booking, not the table definition or the member. There is no selected field list after DELETE. Omitting WHERE deletes every record in the table, subject to constraints. Setting one field to NULL is an update, not deletion of a record. A condition using a primary key is useful when exactly one known record should change.

Selecting targets through related tables

A subquery is a query inside another statement. IN tests membership of its result. To raise the fee once for each workshop that has at least one attended booking:

UPDATE Workshop
SET Fee = Fee + 1.00
WHERE WorkshopID IN (
    SELECT WorkshopID
    FROM Booking
    WHERE Attended = 1
);

The inner query returns IDs from related records; the outer statement updates matching workshops. Even if a workshop ID appears several times in the inner result, membership does not update that workshop repeatedly. This differs from a Python loop that might add 1 for every booking. Likewise, child bookings for Year 11 members can be removed with:

DELETE FROM Booking
WHERE MemberID IN (
    SELECT MemberID
    FROM Member
    WHERE YearGroup = 11
);

These statements change one target table while using another table to determine the target records. SQL dialects also offer other multi-table change syntax, but use a form supported by the stated system. Before a change, use a retrieval query with the same condition to inspect which IDs would be affected.