How should SQLite represent many-to-many relationships?

Use three tables: one for students, one for courses, and a junction table—here, student_courses—to represent the associations. Each row in the junction table means “this student is associated with this course.” The two entity tables stay independent; the relationship is stored in the third table rather than as a list of course IDs inside a student row.

Diagram:

students 1 ──< student_courses >── 1 courses

The “many” on each side comes from allowing multiple junction rows to use the same student ID or course ID. The pair of IDs identifies one association. This structure is useful when the relationship itself is something you need to store as a distinct record.

Assuming this is a small, disposable example, you could use:

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE student_courses (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

The junction table has one foreign key for each entity, and the composite primary key makes the pair the table’s identity. That prevents storing the same student-course pair twice. The explicit NOT NULL declarations make both parts of a relationship required. SQLite requires each referenced parent key to be a primary key or collectively unique. [S1]

This is a starting pattern, not a claim that name or title is the right design for every application. Choose columns and names to fit your actual entities.

Not unless foreign-key enforcement is enabled for the connection. Set it with:

PRAGMA foreign_keys = ON;

Do this for each SQLite connection that should enforce the links; the setting is not a one-time change to the schema. SQLite documents foreign-key enforcement as disabled by default for backward compatibility. With enforcement enabled, each non-NULL child-key value must match a parent row. [S1]

That distinction matters: writing FOREIGN KEY declarations into the table definitions describes the relationship, but you should also make sure the application’s connections turn enforcement on. The source establishes SQLite’s enforcement behavior; it does not decide which entities or relationship rules are appropriate for your particular application.

For the example, the reusable relationship shape is:

students(student_id)       courses(course_id)
          \                 /
           student_courses
           - student_id
           - course_id
           - primary key: (student_id, course_id)

Before using your version, check that each junction-table column points to the intended entity key, both columns are required if partial links make no sense, and the pair—not either ID alone—is the primary key when a pair should appear at most once. Also confirm that the referenced parent columns are primary keys or collectively unique, and that your connections enable foreign-key enforcement. [S1]

If your relationship has additional information, such as a date or role, consider whether that information belongs on the relationship record. The example does not establish what fields your application needs; that depends on what each association means.