How can SQLite prevent duplicate combinations of column values?

Put a single table-level UNIQUE constraint over both columns. For example, assuming person_id and project_id are the identifiers you use, a disposable schema is:

CREATE TABLE assignments (
  person_id  INTEGER NOT NULL,
  project_id INTEGER NOT NULL,
  UNIQUE (person_id, project_id)
);

The rule applies to the combination: a row with (12, 45) and another with (12, 46) have different pairs, as do (12, 45) and (13, 45). Repeating (12, 45) would repeat the pair that the constraint requires to be unique. SQLite permits a table-level UNIQUE constraint on a list of columns, including this kind of pair. [S1]

The NOT NULL declarations are example schema choices, not part of the duplicate-combination rule itself. Replace the sample identifiers and types to fit your design.

Because separate uniqueness rules express a different, stricter requirement. If person_id alone is unique, a person can occur in only one assignment row. If project_id alone is unique, a project can occur in only one assignment row. Together, those rules prevent a person from having multiple projects and a project from having multiple people.

For the many-to-many assignment rule you described, use one constraint on the pair:

UNIQUE (person_id, project_id)

SQLite documents this distinction: uniqueness across a list requires each pair to be unique, while separate uniqueness on each column limits each individual value to at most one appearance. [S1]

Treat these rows as an illustrative example, not as data from a particular database:

person_id | project_id
----------+-----------
12        | 45
12        | 46
13        | 45

All three pairs are different. The pair-based constraint permits these combinations because it does not make either column individually unique. Adding (12, 45) again would duplicate a pair, so it conflicts with the stated rule. By contrast, separate UNIQUE(person_id) and UNIQUE(project_id) rules would reject the example: person 12 appears twice, and project 45 appears twice. [S1]

When checking your own design, write down two test cases: a repeated pair that should be disallowed, and two different pairs sharing one value that should be allowed. That helps reveal whether the constraint matches the rule.

First, confirm that the rule really is “no repeated pair,” rather than “each person can appear once” or “each project can appear once.” Then put both column names in the same table-level UNIQUE constraint. If the table already has rows, consider whether existing repeated pairs need attention before applying the rule; the example does not establish how your schema or migration should handle existing data.

This is a CREATE TABLE example. The SQLite rule described here distinguishes table-level uniqueness across a column list from separate per-column uniqueness; it does not cover every schema detail, migration method, or application-specific assignment rule. [S1]