liveBook · Manning

Foreign key

As explained in Designing AI Systems

A foreign key is a database field or set of fields that references a key in another table to enforce referential integrity.

A foreign key connects a field in one table to a key in another, enforcing referential integrity. In a session service, messages reference sessions through session_id, preventing messages from pointing to nonexistent sessions. section 4.4.1

The relationship also defines deletion behavior. ON DELETE CASCADE removes every message belonging to a deleted session; without it, deletion requires manual cleanup or is rejected to prevent orphaned records. section 4.4.1

Sessions and messages

The messages table declares FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE, while sessions.session_id is the referenced primary key.

Listing 4.10 PostgreSQL schema for the messages table
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,    #A
    session_id VARCHAR(255) NOT NULL,    #B
    role VARCHAR(50) NOT NULL,
    content TEXT,    #C
    tool_calls JSONB,    #D
    tool_call_id VARCHAR(255),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(session_id) 
        ON DELETE CASCADE    #E
);
 
CREATE INDEX idx_messages_session_id ON messages(session_id);

Inspect the complete PostgreSQL messages schema, including the foreign-key constraint and cascading deletion rule. section 4.4.1

How Designing AI Systems covers it

How deep it goes
Defines the constraint and explains cascading deletion, but does not develop broader constraint design or query behavior.
Examples
Uses sessions and messages tables, with messages.session_id referencing sessions.session_id.
What you can do after
Operate/configure PostgreSQL schemas that preserve parent-child relationships and automatically remove dependent rows.
How it connects
Links relational databases with primary keys, referential integrity, and cascading deletes.

Also explained in

Compare how 21 books explain foreign key →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Designing AI Systems. The section links point to the parts of the book it draws on.