{"record":{"id":"38489f32df9ab08c","repo":"pathwaycom/pathway","slug":"primary-key-contains-duplicate-column-s-sorted-d-38489f","errorCode":null,"errorMessage":"primary_key contains duplicate column(s) {sorted(duplicates)}. Each column may appear at most once.","messagePattern":"primary_key contains duplicate column\\(s\\) (.+?)\\. Each column may appear at most once\\.","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/sqlite/__init__.py","lineNumber":370,"sourceCode":"                \"these columns in the Pathway table, or use \"\n                'output_table_type=\"snapshot\".'\n            )\n\n    key_field_names: list[str] | None = None\n    if primary_key is not None:\n        # Duplicate entries in `primary_key` produce a nonsensical SQL\n        # template — e.g. `PRIMARY KEY (\"k\", \"k\")` — which SQLite tolerates,\n        # but the shared `SqlQueryTemplate` then reorders DELETE bindings\n        # using the duplicated index and retractions silently match no\n        # rows. Reject here with a clear message.\n        names_seen: set[str] = set()\n        duplicates: list[str] = []\n        for pkey in primary_key:\n            if pkey.name in names_seen and pkey.name not in duplicates:\n                duplicates.append(pkey.name)\n            names_seen.add(pkey.name)\n        if duplicates:\n            raise ValueError(\n                f\"primary_key contains duplicate column(s) {sorted(duplicates)}. \"\n                \"Each column may appear at most once.\"\n            )\n        key_field_names = []\n        for pkey in primary_key:\n            # Raises ValueError when `pkey` belongs to a different table\n            # or does not name a column of `table`, so users get a clear\n            # message at write() time instead of an opaque runtime error.\n            get_column_index(table, pkey)\n            # Reject nullable primary-key columns. SQLite's\n            # INTEGER PRIMARY KEY auto-assigns a rowid for NULL values,\n            # which can silently collide with a later UPSERT and\n            # overwrite unrelated rows; other PK types let NULLs through\n            # but then DELETE ... WHERE pk = NULL never matches on\n            # retractions. Neither case is what the user asked for.\n            if isinstance(pkey._column.dtype, dt.Optional):\n                raise ValueError(\n                    f\"primary_key column {pkey.name!r} is declared nullable \"","sourceCodeStart":352,"sourceCodeEnd":388,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/sqlite/__init__.py#L352-L388","documentation":"Duplicate entries in primary_key (e.g. [t.k, t.k]) would generate a nonsensical PRIMARY KEY (\"k\", \"k\") clause which SQLite itself tolerates, but Pathway's shared SqlQueryTemplate then reorders DELETE bindings using the duplicated index and retractions silently match no rows — a silent data-corruption bug. The sqlite write() therefore rejects duplicated primary-key column names up front with this ValueError.","triggerScenarios":"Calling pw.io.sqlite.write(..., output_table_type=\"snapshot\", primary_key=[t.k, t.k]) or any primary_key list where the same column reference (by name) appears more than once, e.g. built dynamically from a list with accidental repeats.","commonSituations":"Primary-key lists constructed programmatically from user input or config where the same key appears twice; concatenating key lists (natural key + surrogate key) that overlap; copy-paste of the same column reference into a composite key.","solutions":["Remove the duplicate entry so each column appears exactly once: primary_key=[t.owner, t.pet].","If the key list is built dynamically, de-duplicate it while preserving order before calling write(), e.g. list(dict.fromkeys(names)).","Review whether the duplication came from merging two key specs (natural + surrogate) and drop the redundant one."],"exampleFix":"# before\nkeys = [t.owner, t.pet, t.owner]\npw.io.sqlite.write(t, \"db\", \"snap\", output_table_type=\"snapshot\", primary_key=keys)\n\n# after\nkeys = [t.owner, t.pet]\npw.io.sqlite.write(t, \"db\", \"snap\", output_table_type=\"snapshot\", primary_key=keys)","handlingStrategy":"validation","validationCode":"def dedupe_primary_key(primary_key):\n    seen, unique = set(), []\n    for col in primary_key:\n        if col.name not in seen:\n            seen.add(col.name)\n            unique.append(col)\n    if len(unique) != len(primary_key):\n        raise ValueError(\"primary_key contains duplicate columns\")\n    return unique","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Build composite keys from a set or dict rather than list concatenation when merging key specs.","Unit-test dynamic key construction with inputs that intentionally contain repeats."],"tags":["pathway","sqlite","primary-key","validation"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}