A valid foreign key can still cross a tenant boundary

The ID exists in the wrong tenant

A task belongs to tenant a. Its project_id is 202, a real project owned by tenant b. An ordinary foreign key on project_id accepts that row. Referential integrity holds for the ID, while the application's same-tenant relationship rule is broken.

We ran eight SQL mutations against three SQLite schemas, giving 24 independent checks. Each schema received the same five writes that violate a required same-tenant association. The ID-only schema accepted five, a nullable composite-key schema accepted two and the composite-key schema with both child fields required accepted zero. The full matrix below includes valid and rejection controls.

This distinction still matters with globally unique UUIDs. Uniqueness answers which project a value identifies. A tenant-qualified relationship also requires the identified project to belong to the tenant recorded on the task. Using organization_id, workspace_id or account_id instead of tenant_id changes the vocabulary, not that invariant.

The example assumes every task must belong to a project in its own tenant. Products that permit unassigned tasks or explicit sharing need a different, equally precise relationship contract.

Make the pair the referenced key

Store the relationship as (tenant_id, project_id) and reference the corresponding pair on the parent. Our parent retains a global primary key and adds a unique constraint on (tenant_id, id). This keeps global IDs usable while giving the foreign key an exact tenant-qualified target.

PRAGMA foreign_keys = ON;
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  UNIQUE (tenant_id, id)
);
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id INTEGER NOT NULL,
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects (tenant_id, id)
    ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE INDEX tasks_project ON tasks (tenant_id, project_id);

The SQLite-specific PRAGMA applies to this connection. Parent and child columns match by their declared order. A design with tenant-local project IDs could instead use the tenant/ID pair as the parent primary key, with explicit non-null tenant fields.

In an additional executed check, we removed the parent's composite uniqueness while keeping its globally unique ID. SQLite rejected the first child insert with a foreign-key mismatch error. Declare the referenced pair explicitly. The primary documentation also describes matching column counts and parent-index collation requirements. SQLite foreign-key documentation

RESTRICT makes deletion of a referenced project fail in this fixture. Choose cascading deletion only when deleting its dependent tasks is the intended product behavior. Tenant transfer likewise needs a workflow for the whole relationship graph. Review update cascades before allowing ownership keys to change.

This lab uses immediate constraints. If your design defers a foreign key, require the transaction's commit to succeed before reporting a completed association. Statement acceptance alone then leaves a validation boundary outstanding.

The child index supports locating dependent tasks. Evaluate its actual query plan and write cost with your workload. PostgreSQL documents that creating a foreign key does not automatically create the corresponding child-side index. A constraint test establishes integrity behavior without measuring index performance. PostgreSQL constraints

NULL changes the relationship contract

The nullable composite schema rejected ('a', 202) but accepted (NULL, 202). It also accepted ('a', NULL). In SQLite, a NULL in either child-key column releases that row from the parent-match requirement. Both columns need NOT NULL for the required association tested here.

An optional project association has a different shape. Keep the task's tenant mandatory and allow only project_id to be NULL. A task without a project then has an explicit meaning, while every non-null project reference still includes its tenant. The two nullable schemas in our matrix intentionally allow both fields to be NULL so the escape is visible. That is a comparison input, not a proposed optional-task schema.

PostgreSQL offers MATCH FULL, which rejects a mixture of null and non-null key components while allowing the all-null case. SQLite parses MATCH clauses but applies its ordinary NULL behavior. Adding MATCH FULL to this SQLite example would not enforce a required relationship. Engine-specific behavior belongs in the migration review. PostgreSQL NULL matching, SQLite unsupported MATCH behavior

Deletion policy also interacts with optionality. Nulling the whole pair would discard the tenant as well as the project. PostgreSQL documents a column-specific SET NULL action for that situation. Inspect your engine's supported syntax and preserve the mandatory tenant field when designing an optional reference.

Eight statements across three schemas

Each matrix cell starts with projects (101, 'a') and (202, 'b') in a fresh in-memory database. Update and delete cases first create task (1, 'a', 101). Foreign-key enforcement is on for every matrix cell. We recorded acceptance, SQLite error names and the remaining parent and child rows.

The first row is the positive control. The missing-parent and parent-deletion rows test rejection behavior. Our chart counts only the other five rows, which violate this fixture's required same-tenant contract. It shows accepted statements, with no traffic weighting or production failure-rate estimate.

For rejected updates, the saved task remains (1, 'a', 101). Rejected parent deletion leaves both projects present. Assertions check those final rows as well as the accepted/rejected decision. A generic error response alone would not establish that the earlier state survived.

Download the Python experiment and recorded results. Run python3 experiment.py > results.json using the standard-library sqlite3 module. Our two runs used Python 3.14.7 with SQLite 3.53.4 and produced byte-identical output. Your file records your runtime versions, so a different installation can change those fields.

Check the connection before trusting the schema

With the required composite schema but foreign_keys explicitly off, SQLite accepted task ('a', 202). In the same additional check, we began a transaction and tried to enable foreign keys. Reading the setting still returned 0. After committing and enabling the setting, PRAGMA foreign_key_check reported the stored violation.

Set and verify enforcement before starting a transaction on every SQLite connection used by the application, migrations or imports. Enabling it does not retroactively remove invalid rows. Keep the connection check and the stored-data audit as separate release evidence. The fixture uses autocommit mode to make the transaction boundary explicit. SQLite enforcement settings

These observations are SQLite-specific. The downloadable lab executes real database constraints in memory. A deployment review should run equivalent writes against its own engine, driver and migration path, including any privileged maintenance process that can alter enforcement.

Keep authorization and row visibility in the design

A composite key compares the tenant values stored in related rows. It cannot determine who is issuing the SQL. An unauthorized caller could still name two matching rows belonging to another tenant if the access layer lets that request through. Establish tenant context from an authenticated, authorized membership and enforce object access on reads and writes. Our SaaS permission matrix provides a separate actor/action test pattern.

For the wider isolation design, Pharos Production's guide to tenant-context enforcement in pooled SaaS architecture connects request context and PostgreSQL row-level security with the choice between pooled, bridge and silo data layers. It also explains why the database role affects row-policy enforcement. Use that broader boundary review alongside the relationship checks here.

Keep tenant predicates in lookup and mutation paths, including background jobs. Treat a rejected relationship as a consistency failure with a sanitized client response. Raw constraint details can reveal table names or the existence of another record. Record enough internal context to investigate the writer without returning those details to an untrusted caller.

Migrate the invariant with an explicit stop condition

Before adding the new constraint, inspect existing rows for missing parents, wrong-tenant parents and NULL components. An ID-only foreign key can leave the first category empty while allowing the second. For the fixture's global project IDs, this audit exposes all three categories.

SELECT t.id, t.tenant_id, t.project_id
FROM tasks AS t
LEFT JOIN projects AS p ON p.id = t.project_id
WHERE t.tenant_id IS NULL
   OR t.project_id IS NULL
   OR p.id IS NULL
   OR p.tenant_id <> t.tenant_id;

For tenant-local IDs, join on the complete parent identity and adapt the audit. Resolve each invalid row through a recorded ownership decision. Guessing a tenant during a backfill can convert visible inconsistency into a plausible but incorrect relationship.

1. Review generated migration SQL for the exact parent unique key, non-null child fields and composite reference. Check every writer, including bulk imports and maintenance scripts, for the required tenant value.
2. Plan how the engine prevents new violations while existing data is checked. Validation and locking procedures vary by engine. Use a staged expand-contract migration and keep the old write path's compatibility explicit.
3. Re-run the same-tenant insert, wrong-tenant insert and both update attempts through the deployed driver. Test the intended optional-relationship and deletion policies separately.
4. Block rollout if the existing-data audit finds an unresolved row or an invalid required write is accepted. Preserve the parent key while any deployed child schema still references it.

Add task ('a', 202) to the regression fixture and require the database to reject it. Then require rejection of (NULL, 202) under the mandatory-tenant contract. Those two checks exercise different parts of the schema.

Sources

Documentation checked November 9, 2026.

1. SQLite foreign-key documentation
2. PostgreSQL constraints and NULL matching