{"record":{"id":"ae7425cef30b9d8d","repo":"can1357/oh-my-pi","slug":"sqlite-table-table-has-a-composite-primary-ke","errorCode":null,"errorMessage":"SQLite table '${table}' has a composite primary key; use '?where=' instead","messagePattern":"SQLite table '(.+?)' has a composite primary key; use '\\?where=' instead","errorType":"validation","errorClass":"ToolError","httpStatus":null,"severity":"error","filePath":"packages/coding-agent/src/tools/sqlite-reader.ts","lineNumber":710,"sourceCode":"\nexport function getTablePrimaryKey(db: Database, table: string): { column: string; type: string } | null {\n\tconst primaryKeyColumns = getPrimaryKeyColumns(db, table);\n\tif (primaryKeyColumns.length !== 1) {\n\t\treturn null;\n\t}\n\n\tconst column = primaryKeyColumns[0]!;\n\treturn { column: column.name, type: column.type };\n}\n\nexport function resolveTableRowLookup(db: Database, table: string): SqliteRowLookup {\n\tconst primaryKeyColumns = getPrimaryKeyColumns(db, table);\n\tif (primaryKeyColumns.length === 1) {\n\t\tconst column = primaryKeyColumns[0]!;\n\t\treturn { kind: \"pk\", column: column.name, type: column.type };\n\t}\n\tif (primaryKeyColumns.length > 1) {\n\t\tthrow new ToolError(`SQLite table '${table}' has a composite primary key; use '?where=' instead`);\n\t}\n\n\tconst schema = getTableSchema(db, table);\n\tif (/\\bWITHOUT\\s+ROWID\\b/i.test(schema)) {\n\t\tthrow new ToolError(`SQLite table '${table}' does not expose ROWID; use '?where=' instead`);\n\t}\n\n\treturn { kind: \"rowid\" };\n}\n\nexport function queryRows(\n\tdb: Database,\n\ttable: string,\n\topts: { limit: number; offset: number; order?: string; where?: string },\n): { columns: string[]; rows: Record<string, unknown>[]; totalCount: number } {\n\tconst columns = getTableColumns(db, table);\n\tconst validatedWhere = validateWhereClause(opts.where);\n\tconst whereClause = validatedWhere ? ` WHERE ${validatedWhere}` : \"\";","sourceCodeStart":692,"sourceCodeEnd":728,"githubUrl":"https://github.com/can1357/oh-my-pi/blob/969062200754ea02cfac922e5ebb8c608c079e15/packages/coding-agent/src/tools/sqlite-reader.ts#L692-L728","documentation":"resolveTableRowLookup determines how a single row can be addressed (`table:key`). A single-column primary key maps to `kind: pk`; when the table declares a composite (multi-column) primary key, no single value can identify a row, so the tool throws and points at the `?where=` selector instead.","triggerScenarios":"Calling resolveTableRowLookup (indirectly via a `db.sqlite:table:key` selector) against a table whose CREATE TABLE has PRIMARY KEY (a, b) or multiple column-level PRIMARY KEYs.","commonSituations":"Join tables / many-to-many mapping tables (user_id, role_id); ORM-generated schemas with composite keys; trying row lookup on a table that was designed to be addressed by a tuple.","solutions":["Use the query selector with a where clause: `db.sqlite:join_table?where=user_id=1 AND role_id=2`","Use a raw SQL query: `db.sqlite?q=SELECT * FROM join_table WHERE user_id=1 AND role_id=2`","If single-row addressing is needed, alter the schema to use a single-column primary key or a UNIQUE single column"],"exampleFix":"// before\nsqlite://app.db?user_roles:5:9\n// after\nsqlite://app.db?user_roles?where=user_id=5 AND role_id=9","handlingStrategy":"validation","validationCode":"const pkCols = db\n  .query(`PRAGMA table_info(${quoteSqliteIdentifier(table)})`)\n  .all()\n  .filter(c => c.pk);\nif (pkCols.length > 1) {\n  // composite key: use where= selector instead of table:key\n}","typeGuard":null,"tryCatchPattern":"try {\n  const lookup = resolveTableRowLookup(db, table);\n} catch (err) {\n  if (err instanceof ToolError && err.message.includes(\"composite primary key\")) {\n    // route caller to ?where=col1=v1 AND col2=v2\n  } else throw err;\n}","preventionTips":["Check PRAGMA table_info pk flags before attempting table:key lookups","Use ?where= for any composite-key table (join/mapping tables)","Prefer schemas with a single-column primary key or rowid for point lookups"],"tags":["sqlite","primary-key","schema"],"backgroundTag":"composite-primary-key","analyzedSha":"969062200754ea02cfac922e5ebb8c608c079e15","analyzedAt":"2026-08-31T10:29:35.737Z","schemaVersion":2},"datasetVersion":"2026-08-31T14:17:45.589Z"}