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




















