{"record":{"id":"52d4f2f250ecbd30","repo":"pathwaycom/pathway","slug":"primary-key-contains-duplicate-column-s-sorted-d-52d4f2","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":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/mssql/__init__.py","lineNumber":457,"sourceCode":"        table_writer_init_mode=init_mode_from_str(init_mode),\n        snapshot_maintenance_on_output=is_snapshot_mode,\n    )\n\n    key_field_names = None\n    if primary_key is not None:\n        # Duplicate entries in `primary_key` produce a nonsensical\n        # `PRIMARY KEY ([x], [x])` SQL clause that SQL Server rejects, and\n        # the shared `SqlQueryTemplate` reorders DELETE bindings using the\n        # duplicated index so retractions silently bind wrong values.\n        # Reject 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 or\n            # 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.  SQL Server refuses to\n            # build a PRIMARY KEY on a nullable column, and even if the\n            # destination table is hand-crafted to allow NULLs, the MERGE\n            # statement uses `target.k = source.k` which is UNKNOWN (not\n            # TRUE) when both sides are NULL — so retractions never match.\n            if isinstance(pkey._column.dtype, dt.Optional):\n                raise ValueError(\n                    f\"primary_key column {pkey.name!r} is declared nullable \"\n                    f\"({pkey._column.dtype}); primary-key columns must be \"","sourceCodeStart":439,"sourceCodeEnd":475,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/mssql/__init__.py#L439-L475","documentation":"In snapshot mode, pw.io.mssql.write builds a PRIMARY KEY ([x], [x]) clause from the primary_key list; duplicate entries would make SQL Server reject the CREATE TABLE, and worse, the shared SqlQueryTemplate reorders DELETE bindings using the duplicated index so retractions silently bind the wrong values. This ValueError is raised at write() time listing the duplicated column names.","triggerScenarios":"Passing the same column reference twice in primary_key, e.g. primary_key=[table.id, table.id], or building the list programmatically so the same column ends up in it more than once.","commonSituations":"Concatenating key lists from several sources without deduplication (primary_key=keys_a + keys_b); refactoring a composite key list and forgetting to remove the old entry.","solutions":["Deduplicate while preserving order: primary_key=list(dict.fromkeys(primary_key)).","Fix the list construction upstream so the same column is not added twice.","Review composite-key assembly code (unions, config merges) for accidental duplicates."],"exampleFix":"# before\nprimary_key = tenant_keys + tenant_keys  # accidental duplication\npw.io.mssql.write(t, \"t\", output_table_type=\"snapshot\", primary_key=primary_key)\n\n# after\nprimary_key = list(dict.fromkeys(tenant_keys + tenant_keys))\npw.io.mssql.write(t, \"t\", output_table_type=\"snapshot\", primary_key=primary_key)","handlingStrategy":"validation","validationCode":"primary_key = list(dict.fromkeys(primary_key))  # dedupe, preserve order\nnames = [c._name for c in primary_key]\nassert len(names) == len(set(names)), \"duplicate primary_key entries\"","typeGuard":"def has_unique_pk_names(primary_key) -> bool:\n    names = [c._name for c in primary_key]\n    return len(names) == len(set(names))","tryCatchPattern":"try:\n    pw.io.mssql.write(t, \"t\", output_table_type=\"snapshot\", primary_key=keys)\nexcept ValueError as e:\n    if \"duplicate column\" in str(e):\n        keys = list(dict.fromkeys(keys))\n        pw.io.mssql.write(t, \"t\", output_table_type=\"snapshot\", primary_key=keys)\n    else:\n        raise","preventionTips":["Deduplicate key lists with dict.fromkeys() whenever assembling keys from multiple sources.","Keep composite-key definitions in one place instead of concatenating ad-hoc lists.","Duplicated keys corrupt DELETE bindings silently in some templates — never ignore this error."],"tags":["mssql","primary-key","duplicate","snapshot","sink","pathway"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}