Found while implementing #11202 (which unified the three arms on single-column-uniqueness semantics). This is the residual cross-dialect divergence in the same method, left deliberately untouched there because it is a semantic question rather than a mechanical one — the flag it produces is not false, only inconsistent.
What
SqlDriver.introspectUniqueConstraints reaches its answer from a different catalog per dialect, and the two catalogs disagree about whether a PRIMARY KEY is a unique constraint:
- Postgres / MySQL filter on
CONSTRAINT_TYPE = 'UNIQUE', which excludes primary keys outright. A primary key is never reported through this method on either dialect. - SQLite iterates
PRAGMA index_list and keys only on idx.unique === 1, never on origin. SQLite materialises a non-INTEGER primary key as a unique auto-index, so the primary-key column is reported.
Measured on embedded better-sqlite3 (2026-08-24):
create table t_text (id varchar(64) primary key, email varchar(64) unique)
PRAGMA index_list(t_text) ->
{ name: 'sqlite_autoindex_t_text_2', unique: 1, origin: 'u' } -- the UNIQUE(email)
{ name: 'sqlite_autoindex_t_text_1', unique: 1, origin: 'pk' } -- the PRIMARY KEY(id)
create table t_int (id integer primary key, note varchar(64))
PRAGMA index_list(t_int) -> []
So introspectSchema sets isUnique: true on t_text.id, and the same table introspected through Postgres or MySQL does not.
The second half: SQLite disagrees with itself
t_int shows the divergence is not even stable within SQLite. An INTEGER PRIMARY KEY is a rowid alias, so SQLite creates no auto-index and index_list is empty — the column is not flagged. A varchar primary key is flagged. The same logical schema therefore produces different isUnique flags depending only on the key's declared type.
Why it matters
The same consumer argument as #11202: the flag reaches introspectedSchemaToObjects in @objectstack/objectql, which turns it into a drafted field's unique: true, and from there into cross-dialect snapshot comparison and the ADR-0015 federated-object draft. A drafted object gains or loses a unique: true on its key column according to which dialect it was drafted from, and according to whether that key is an INTEGER.
Note the direction: unlike #11202 this flag is not lying — a primary-key column really is unique. What is wrong is that three dialects (four, counting SQLite's two cases) answer differently.
Decision needed
Should a PRIMARY KEY column carry isUnique?
- A — never, matching the PG/MySQL arms: filter the SQLite arm on
origin !== 'pk'. Primary-key membership is already reported separately and losslessly through primaryKeys / IntrospectedColumn.primaryKey, so nothing is lost, and isUnique becomes purely "unique constraint that is not the key". Mechanical: origin is already in the index_list rows the arm reads. - B — always, matching SQLite's
varchar case: report a single-column primary key as unique on all three dialects. Truthful in the strict sense (a PK is unique), and arguably more useful to a consumer asking "can I address a row by this column alone" — but it means adding a primary-key read to the PG and MySQL arms, and it makes isUnique partly redundant with primaryKey.
A is the smaller change and keeps the two flags non-overlapping; B is defensible if any consumer wants "uniquely addressable" as one question. Either way the SQLite self-inconsistency between INTEGER and varchar keys should go.
Filed unassigned, per the finding rule. Not fixed in #11202's PR: the correct form is not pinned by existing evidence, so it fails the bounded in-place exemption's second condition.
Generated by Claude Code
Found while implementing #11202 (which unified the three arms on single-column-uniqueness semantics). This is the residual cross-dialect divergence in the same method, left deliberately untouched there because it is a semantic question rather than a mechanical one — the flag it produces is not false, only inconsistent.
What
SqlDriver.introspectUniqueConstraintsreaches its answer from a different catalog per dialect, and the two catalogs disagree about whether a PRIMARY KEY is a unique constraint:CONSTRAINT_TYPE = 'UNIQUE', which excludes primary keys outright. A primary key is never reported through this method on either dialect.PRAGMA index_listand keys only onidx.unique === 1, never onorigin. SQLite materialises a non-INTEGER primary key as a unique auto-index, so the primary-key column is reported.Measured on embedded better-sqlite3 (2026-08-24):
So
introspectSchemasetsisUnique: trueont_text.id, and the same table introspected through Postgres or MySQL does not.The second half: SQLite disagrees with itself
t_intshows the divergence is not even stable within SQLite. AnINTEGER PRIMARY KEYis a rowid alias, so SQLite creates no auto-index andindex_listis empty — the column is not flagged. Avarcharprimary key is flagged. The same logical schema therefore produces differentisUniqueflags depending only on the key's declared type.Why it matters
The same consumer argument as #11202: the flag reaches
introspectedSchemaToObjectsin@objectstack/objectql, which turns it into a drafted field'sunique: true, and from there into cross-dialect snapshot comparison and the ADR-0015 federated-object draft. A drafted object gains or loses aunique: trueon its key column according to which dialect it was drafted from, and according to whether that key is anINTEGER.Note the direction: unlike #11202 this flag is not lying — a primary-key column really is unique. What is wrong is that three dialects (four, counting SQLite's two cases) answer differently.
Decision needed
Should a PRIMARY KEY column carry
isUnique?origin !== 'pk'. Primary-key membership is already reported separately and losslessly throughprimaryKeys/IntrospectedColumn.primaryKey, so nothing is lost, andisUniquebecomes purely "unique constraint that is not the key". Mechanical:originis already in theindex_listrows the arm reads.varcharcase: report a single-column primary key as unique on all three dialects. Truthful in the strict sense (a PK is unique), and arguably more useful to a consumer asking "can I address a row by this column alone" — but it means adding a primary-key read to the PG and MySQL arms, and it makesisUniquepartly redundant withprimaryKey.A is the smaller change and keeps the two flags non-overlapping; B is defensible if any consumer wants "uniquely addressable" as one question. Either way the SQLite self-inconsistency between
INTEGERandvarcharkeys should go.Filed unassigned, per the finding rule. Not fixed in #11202's PR: the correct form is not pinned by existing evidence, so it fails the bounded in-place exemption's second condition.
Generated by Claude Code