How can a SQLite schema represent an optional relationship?

Put the foreign key on the table whose rows may or may not point to a parent—in this example, task. Make that column nullable, rather than requiring every task to have a project:

CREATE TABLE project (
  id INTEGER PRIMARY KEY
);

CREATE TABLE task (
  id INTEGER PRIMARY KEY,
  project_id INTEGER REFERENCES project(id)
);

Here, a task with project_id set to NULL has no project; a non-NULL value must match a project.id. SQLite treats a NULL child key as satisfying the foreign key. [S1] This represents an optional link from a task to a project. It does not, by itself, say that every project must have a task, or limit how many tasks can refer to one project.

If keeping the tasks and removing their project association is the intended result, add ON DELETE SET NULL to the foreign key:

project_id INTEGER REFERENCES project(id) ON DELETE SET NULL

When a referenced project is deleted, SQLite sets the matching child key to NULL. [S1] That result must still satisfy the column’s other constraints and foreign-key rules, so do not combine this choice with a rule that forbids NULL in project_id. [S1] This action changes the relationship, not the task’s other data. If instead you want deleting a project to remove its tasks, that is a different design choice; do not select SET NULL for that outcome.

Start with the rule for a single task: can it validly exist without a project? If yes, allow NULL; if no, the relationship should be required and the column should not permit NULL. Then decide what a project deletion means for tasks. SQLite documents NO ACTION as the default when no action is specified, and lists RESTRICT, SET NULL, SET DEFAULT, and CASCADE as other actions. [S1] Choose based on the intended outcome, not convenience: SET NULL means keep the child without its parent. SET DEFAULT depends on the resulting default satisfying the schema’s constraints and foreign-key rules. [S1]

A useful decision note for the example is:

Can a task exist without a project? Yes.
Can project_id be NULL? Yes.
When a project is deleted, should tasks remain unassigned? Yes: choose ON DELETE SET NULL.

If your answers differ, change the column rule or delete action accordingly.

Check both the design and the application behavior. A declaration in the schema describes the relationship, but it does not establish that foreign-key enforcement is enabled for every application or connection. [S1] Confirm that enforcement is enabled in the environment that uses the database, then check that NULL and matching parent values are accepted and that a missing parent is rejected. For SET NULL, test deleting a referenced project and confirm the task remains with a NULL project_id. These are verification steps for your own database, not results guaranteed by the example.