How do SQLite foreign keys connect child rows to parent rows?

Put the REFERENCES constraint on the task table: tasks is the child, and projects is the parent. The child column stores the value that refers to the parent’s key. The parent key must use named columns, not the rowid, and must be a primary key or have a qualifying UNIQUE constraint or unique index. [S1]

For example, assuming each project has an integer project_id and tasks belong to one project:

CREATE TABLE projects (
  project_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE tasks (
  task_id INTEGER PRIMARY KEY,
  project_id INTEGER REFERENCES projects(project_id),
  title TEXT NOT NULL
);

Here, tasks.project_id is the child key and projects.project_id is the parent key. The constraint describes the relationship; it does not mean the task row contains the project’s other fields.

For a non-NULL child key, its value must match the corresponding parent-key value under SQLite’s comparison rules. [S1] Suppose the projects table contains a row with project_id 12. A task with project_id 12 has a matching parent reference. If there is no parent row whose key matches a task’s value, that value has no corresponding parent and does not meet the reference condition.

For this example, the parent key is the single column project_id, so the comparison is one value to one value. With a parent key made of multiple columns, each child-key value must match its corresponding parent-key value. This guide explains the schema relationship and what counts as a match; it does not cover retrieving related rows.

With the schema above, project_id is nullable because it was not declared NOT NULL. SQLite does not require a matching parent row when any child-key column is NULL. [S1] If every task must refer to a project, declare the child column NOT NULL as well:

project_id INTEGER NOT NULL REFERENCES projects(project_id)

Assuming the only project keys are 12 and 15, a task whose project_id is 99 has no matching parent key, so its reference is invalid. A task whose project_id is NULL has no parent match, but the NULL rule means a parent row is not required. [S1]

Check that the REFERENCES clause is on the child table, that it names the intended parent table and columns, and that those parent columns qualify as a key. Then decide whether an unmatched child should be allowed to have NULL. In the example, projects.project_id is the parent primary key; tasks.project_id refers to it; and NOT NULL is appropriate only if every task is required to have a project.

A compact review example:

-- Parent key: projects.project_id
-- Child reference: tasks.project_id
-- Matching value: 12, if a parent with key 12 exists
-- Unmatched value: 99, if no parent with key 99 exists
-- Optional reference: NULL, when the child key is nullable

These comments describe the intended relationship. The schema example shows the parent key, child reference, and the difference between matching, unmatched, and nullable child-key values; it does not show how to retrieve related records.