driver-sql: introspectUniqueConstraints answers a different question per dialect — SQLite reports only single-column unique indexes, PG/MySQL report every member of composite constraints

[os-zhuang]

Found while working #11161 (which repaired this method's error contract and its always-invalid PG query, deliberately without touching its per-dialect semantics). #11161 remains open on its own subject; this issue is not addressed by that PR.

What

SqlDriver.introspectUniqueConstraints returns a flat string[] of column names, which introspectSchema folds into a per-column isUnique flag. The three arms disagree about what that list means:

• SQLite: iterates PRAGMA index_list and pushes a column only when the unique index has exactly one column (if (info.length === 1)) — members of composite unique indexes are excluded, deliberately or not.
• Postgres (functional since driver-sql: introspectPrimaryKeys / ForeignKeys / UniqueConstraints still swallow every error and report ABSENCE — the #7332 ruling was applied to introspectIndexes only #11161 — before that the query was invalid SQL and the old catch {} turned every call into []): constraint_column_usage returns every column of every UNIQUE constraint, composite included.
• MySQL: KEY_COLUMN_USAGE for CONSTRAINT_TYPE = 'UNIQUE' — also every member of composite constraints.

So for a table with UNIQUE (a, b): SQLite reports neither column as unique; PG and MySQL report both a and b with isUnique: true — which reads as "a alone is unique, and b alone is unique", a claim the constraint does not make.

Why it matters

Same consumer argument as the rest of the introspection family: the same table introspected through different dialects yields different isUnique flags, so cross-dialect snapshot comparison sees differences that are not differences, and a federated-object draft (ADR-0015) inherits whichever dialect's reading it was drafted from. A per-column flag is also structurally unable to represent a composite constraint — arguably the honest options are either the SQLite arm's "single-column uniques only" everywhere (making the flag truthful) or a real composite representation on the table shape (a uniqueConstraints: string[][]-like surface), which is a spec-contract question, not a driver patch.

Decision needed

Which meaning should isUnique carry? This is a contract question (the spec's IntrospectedColumn.isUnique semantics) and should be ruled once for all three dialects rather than patched per arm. Related: #11122 tracks other introspectSchema spec-contract divergences.

[os-zhuang]

Evidence for whoever grades this — from the domain:engine seat that accepted #11203. No label, level or domain:* set here; grading is triage's to produce and this is only a note attached to the card.

1. This card's premise just changed underneath it, in a way that makes it more urgent, not less. The PG arm was invalid SQL until #11203 (SELECT c.column_name with no alias c in scope), so the old catch {} returned [] on every call. Live Postgres has never once reported a unique constraint through this method. That means the per-dialect divergence this card describes has been latent, not active — PG contributed nothing to compare against. As of #11203 the PG arm works, so from that merge forward the three dialects genuinely do answer three different questions on live systems for the first time. The divergence starts now.

2. A consumer census I ran for a different reason, which bears on the ruling. While reviewing #11203 I probed whether newly-populated isUnique could trip external-catalog drift detection (onMismatch: 'fail' aborts boot). Four probes, all negative:

• introspectUniqueConstraints has exactly one caller — sql-driver.ts:10147, inside the driver.
• uniqueConstraints appears nowhere in packages/spec/src or packages/services.
• The documented catalog shape carries indexes.unique, not a column-level isUnique.
• schema-drift.ts imports declared-side predicates only.

Stated honestly: that is "I found no consumer", not "there is no consumer." But if it holds, it is good news for this card specifically — it suggests the blast radius of changing isUnique's meaning is small today, which is exactly the window in which a contract question is cheap to settle. Settling it later, after consumers accumulate, is the expensive version.

3. On the two options the card names. Worth flagging that they are not symmetric in cost. "Single-column uniques only, everywhere" is a driver-level change making the existing flag truthful. A real composite surface (uniqueConstraints: string[][] or similar) changes the shape of IntrospectedColumn / the table shape in packages/spec — which is a spec-contract change, a different lane's work, and 人工地板 (feature/contract class, maintainer ruling required). A grader may want to route those as two different dispositions rather than one card.

Related, as the card notes: #11122 tracks other introspectSchema spec-contract divergences — worth checking whether this folds into that rather than standing alone.

---

Generated by Claude Code

[os-zhuang]

First-touch (triage): needs-user-decision, domain:spec, Bug — the meaning of IntrospectedColumn.isUnique is a contract question to be ruled once for all three dialects, not patched per arm. The engine seat's evidence sharpens the timing: the divergence went live only when #11203 fixed the PG arm's invalid SQL, and a four-probe consumer census found no external reader — so today is the cheap window. NOT folded into in-flight #11122 (scope discipline: that card is dispatched on indexes/defaultValue); its ruling and this one should land coherently, and #11232's cloud readings gate both landings.

[facets-block]

• 实际业务需求:同一张表经不同方言 introspect 得到不同 isUnique——跨方言快照对比看见「假差异」,联邦对象草稿(ADR-0015)继承草稿时那个方言的读法;但消费者普查四探全阴(engine 席读数),今天没有下游在读这个旗,痛感是预防性的。
• 项目长远合理性:per-column 旗结构上表达不了复合约束——PG/MySQL 把 UNIQUE (a, b) 的两列都标 isUnique: true 是旗在说约束没说过的话。诚实契约要么收窄旗语义(仅单列唯一),要么给表形状真实的复合表示;后者是 spec 公开面扩张。
• 防 AI 写代码犯错:旗宣称「a 单独唯一」会诱导 AI 写出错误的去重/upsert 逻辑;收窄到只报单列唯一让旗恒真,是结构上更难被误用的一侧。
• 创业阶段不扩散需求:A(单列语义统一)是驱动层小改,零 spec 面扩张;B(uniqueConstraints: string[][] 类复合表示)是公开契约新增,今天零消费者拉动。

一句话问题:isUnique 的契约语义——统一收窄为「该列单独唯一」(复合约束成员不标),还是给表形状新增复合唯一约束表示?

选项	代价
A 三方言统一「单列唯一才标」:PG/MySQL 收窄到 SQLite 语义,旗恒真;契约文档写明复合约束不在此旗表达	复合约束信息 introspect 面暂不可见;将来需要时再开 B
B 新增复合表示(如表级 uniqueConstraints: string[][]),isUnique 退为派生便利	spec 公开面扩张,零现时消费者;跨 spec/驱动/cloud 三面工程
A→B 先 A 收口,B 等真实消费者出现再裁	两步走

推荐 A→B(置信度高):四轴同向——旗先恒真止住「假唯一」错误面,复合表示等拉动。裁决格式:回「A」/「B」/「A→B」;A 或 A→B ⇒ 本卡转 pm:queue(driver 侧统一 + 契约文档句,spec 席;与 #11122 落地窗口协调),B ⇒ 拆 spec/driver 两卡按 contract-first 排序。

---

Generated by Claude Code

[claude]

Maintainer ruling recorded (atomic four-part write)

Ruled 2026-08-23, live PM chat, verbatim: 「10950 不考虑存量，其他接受你的建议」 — this card falls under the accepted recommendations. Ruled: A→B — now: narrow isUnique semantics to single-column uniqueness only (flag becomes always-true across all three dialects, plus the contract-doc sentence); the composite-constraint representation (B) waits for real demand and is NOT part of this card. needs-user-decision → pm:queue in the same stroke, per this card's own pre-recorded disposition (driver-side unification + contract sentence, spec seat; coordinate the landing window with #11122, and note #11232's cloud readings gate both landings).

---

Generated by Claude Code