{"record":{"id":"1ccdf9f6281890e9","repo":"pathwaycom/pathway","slug":"primary-key-can-only-be-specified-for-the-snapshot-1ccdf9","errorCode":null,"errorMessage":"primary_key can only be specified for the snapshot table type","messagePattern":"primary_key can only be specified for the snapshot table type","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/mssql/__init__.py","lineNumber":385,"sourceCode":"    >>> pw.io.mssql.write(\n    ...     table,\n    ...     \"Server=tcp:localhost,1433;Database=testdb;\"\n    ...         \"User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=true\",\n    ...     table_name=\"test_snapshot\",\n    ...     init_mode=\"create_if_not_exists\",\n    ...     output_table_type=\"snapshot\",\n    ...     primary_key=[table.key],\n    ... )\n\n    You can run this pipeline with ``pw.run()``.\n    \"\"\"\n\n    _validate_identifier(\"table_name\", table_name)\n    _validate_identifier(\"schema_name\", schema_name)\n\n    is_snapshot_mode = output_table_type == SNAPSHOT_OUTPUT_TABLE_TYPE\n    if not is_snapshot_mode and primary_key is not None:\n        raise ValueError(\n            \"primary_key can only be specified for the snapshot table type\"\n        )\n\n    value_fields = _format_output_value_fields(table)\n\n    # SQL Server's default collation matches identifiers case-insensitively\n    # (`id` and `ID` resolve to the same column), so any pair of schema\n    # columns that differ only in case would make CREATE TABLE fail with a\n    # raw \"duplicate column name\" driver error at pipeline-startup.  Surface\n    # the collision here with a Pathway-authored message instead.\n    case_groups: dict[str, list[str]] = {}\n    for field in value_fields:\n        case_groups.setdefault(field.name.lower(), []).append(field.name)\n    case_collisions = [\n        sorted(names) for names in case_groups.values() if len(names) > 1\n    ]\n    if case_collisions:\n        raise ValueError(","sourceCodeStart":367,"sourceCodeEnd":403,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/mssql/__init__.py#L367-L403","documentation":"In pw.io.mssql.write, the primary_key argument (which builds a PRIMARY KEY constraint on the destination snapshot table) is only meaningful for output_table_type=\"snapshot\". In the default stream_of_changes mode the output is an append-only change log with time/diff metadata, so a primary key makes no sense and is rejected at write() time.","triggerScenarios":"Calling pw.io.mssql.write(table, table_name, primary_key=[table.id]) without also passing output_table_type=\"snapshot\"; the default output_table_type is stream_of_changes, so any primary_key argument with the default triggers this error.","commonSituations":"Copying the primary_key argument from a snapshot-mode example while leaving the table type default; incrementally adding a primary key to an existing sink call without reading the mode parameter.","solutions":["Add output_table_type=\"snapshot\" to the write() call if you want a keyed, upserted destination table.","Or remove the primary_key argument if you intend to keep stream_of_changes output."],"exampleFix":"# before\npw.io.mssql.write(table, \"events\", primary_key=[table.id])\n\n# after\npw.io.mssql.write(\n    table,\n    \"events\",\n    output_table_type=\"snapshot\",\n    primary_key=[table.id],\n)","handlingStrategy":"validation","validationCode":"if primary_key is not None:\n    assert output_table_type == \"snapshot\", (\n        \"primary_key requires output_table_type='snapshot' in pw.io.mssql.write\"\n    )","typeGuard":"from typing import Any\n\ndef is_valid_mssql_write_config(output_table_type: str, primary_key: Any) -> bool:\n    return primary_key is None or output_table_type == \"snapshot\"","tryCatchPattern":"try:\n    pw.io.mssql.write(table, \"t\", output_table_type=mode, primary_key=keys)\nexcept ValueError as e:\n    if \"snapshot table type\" in str(e):\n        pw.io.mssql.write(table, \"t\", output_table_type=\"snapshot\", primary_key=keys)\n    else:\n        raise","preventionTips":["Encapsulate sink configuration: if primary_key is provided, always set output_table_type='snapshot' in the same helper.","Read the write() docstring before mixing primary_key with the default table type.","Keep one canonical example per output mode in your project docs."],"tags":["mssql","primary-key","output-mode","sink","pathway"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}