warn-fk-nondeterministic-collation

What triggers it

A foreign key constraint being added — AT_AddConstraint with CONSTR_FOREIGN in ALTER TABLE, or an inline FOREIGN KEY/REFERENCES in CREATE TABLE — where the migration's SQL also matches a non-deterministic ICU collation pattern (und-x-icu and similar). Checked only when ctx.pgVersion is 18 or higher.

What does not

Migrations targeting PostgreSQL below 18. For the ALTER TABLE path, an FK constraint where no statement anywhere in the migration matches the non-deterministic-collation pattern. For CREATE TABLE, a statement with no FOREIGN KEY/REFERENCES text, or one where the collation pattern doesn't appear in that same statement.

Where it applies

Applies to PostgreSQL 18 and later. It works on the SQL text alone — no database connection needed.

A non-deterministic collation on an FK column was already ambiguous before PG18 — rows could match incorrectly. PG18 changes what happens next: it rejects the constraint outright at creation time instead of accepting it silently.

The lock, and what it blocks

None — the risk is a rejected statement or an ambiguous match, not lock contention.

Why it matters

PostgreSQL 18 validates that FK columns use deterministic collations. Non-deterministic collations (like ICU case-insensitive) can cause FK lookups to match incorrect values. PG18 rejects such FKs.

Unsafe, and safe

CREATE TABLE orders (
  code TEXT COLLATE "und-x-icu",
  FOREIGN KEY (code) REFERENCES products(code)
);

CREATE TABLE orders (
  code TEXT COLLATE "C",
  FOREIGN KEY (code) REFERENCES products(code)
);

What it assumes

For the ALTER TABLE path, it scans the whole migration's SQL text for a non-deterministic collation rather than tracing which column the FK actually references — an unrelated ICU column elsewhere in the same file can trigger a false positive on an FK that has nothing to do with it.

What the CLI prints

⚠ [MP083] WARNING (line 1)
  CREATE TABLE "orders" has FK constraint with non-deterministic collation. PG18 rejects FKs with non-deterministic collations.

  Safe alternative:
  -- Use a deterministic collation for FK columns:
  -- Change COLLATE "und-x-icu" to COLLATE "C" or a deterministic locale

  Why: PostgreSQL 18 now validates that foreign key columns use deterministic collations. Non-deterministic collations (like ICU case-insensitive) can cause FK lookups to match incorrect values, e.g. "abc" matching "ABC". PG18 rejects such FKs at creation time.
  Docs: https://migrationpilot.dev/rules/mp083

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP083
CREATE TABLE orders (

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP083: false

# or keep it, and downgrade it
rules:
  MP083:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.