{"record":{"id":"1daf95d8ad6615ce","repo":"pathwaycom/pathway","slug":"primary-key-column-pkey-name-r-is-declared-nulla-1daf95","errorCode":null,"errorMessage":"primary_key column {pkey.name!r} is declared nullable ({pkey._column.dtype}); primary-key columns must be non-nullable in snapshot mode.","messagePattern":"primary_key column (.+?) is declared nullable \\((.+?)\\); primary-key columns must be non-nullable in snapshot mode\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/mssql/__init__.py","lineNumber":473,"sourceCode":"            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 \"\n                    \"non-nullable in snapshot mode.\"\n                )\n            key_field_names.append(pkey.name)\n    data_format = api.DataFormat(\n        format_type=\"identity\",\n        key_field_names=key_field_names,\n        value_fields=value_fields,\n    )\n\n    datasink_type = \"snapshot\" if is_snapshot_mode else \"sink\"\n    table.to(\n        datasink.GenericDataSink(\n            data_storage,\n            data_format,\n            datasink_name=f\"mssql.{datasink_type}\",\n            unique_name=name,","sourceCodeStart":455,"sourceCodeEnd":491,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/mssql/__init__.py#L455-L491","documentation":"In snapshot mode, pw.io.mssql.write creates a PRIMARY KEY constraint on the destination table, and SQL Server refuses to build one on a nullable column. Additionally, the MERGE statement used for upserts matches with target.k = source.k, which is UNKNOWN (not TRUE) when both sides are NULL, so retractions would never match. Pathway therefore rejects nullable primary-key columns at write() time.","triggerScenarios":"Passing primary_key=[table.col] where table.col has an Optional dtype (e.g. it came from a left join, an optional schema column, or a column that allows None), together with output_table_type=\"snapshot\".","commonSituations":"Using a join result column as the snapshot key (join keys become Optional after outer joins); building the output table from a schema where the key column was declared | None; upstream .unwrap() forgotten after an optional computation.","solutions":["Make the key column non-nullable before the sink, e.g. with pw.coalesce(table.id, default) or by filtering/recomputing so the dtype is not Optional.","Choose a different key column that is guaranteed non-nullable (e.g. this_row.id or a required column from the source schema).","If NULL keys genuinely occur, decide on a sentinel/default value strategy and apply it explicitly before write()."],"exampleFix":"# before\npw.io.mssql.write(joined, \"t\", output_table_type=\"snapshot\", primary_key=[joined.user_id])\n# joined.user_id is Optional after a left join\n\n# after\nsnapshot = joined.with_columns(user_id=pw.coalesce(joined.user_id, -1))\npw.io.mssql.write(snapshot, \"t\", output_table_type=\"snapshot\", primary_key=[snapshot.user_id])","handlingStrategy":"validation","validationCode":"import pathway as pw\n\nnullable_keys = [c for c in primary_key if isinstance(c._column.dtype, pw.dt.Optional)]\nif nullable_keys:\n    table = table.with_columns(\n        **{c._name: pw.coalesce(table[c._name], -1) for c in nullable_keys}\n    )","typeGuard":"import pathway as pw\n\ndef is_non_nullable(col_ref) -> bool:\n    return not isinstance(col_ref._column.dtype, pw.dt.Optional)","tryCatchPattern":"try:\n    pw.io.mssql.write(t, \"t\", output_table_type=\"snapshot\", primary_key=[t.user_id])\nexcept ValueError as e:\n    if \"non-nullable\" in str(e):\n        t = t.with_columns(user_id=pw.coalesce(t.user_id, -1))\n        pw.io.mssql.write(t, \"t\", output_table_type=\"snapshot\", primary_key=[t.user_id])\n    else:\n        raise","preventionTips":["Expect join keys to become Optional after outer joins; coalesce or filter before snapshot sinks.","Verify key column dtypes in a debug print (table.schema) before configuring the sink.","Remember MERGE semantics: NULL = NULL never matches, so nullable keys break retractions."],"tags":["mssql","primary-key","nullable","snapshot","merge","pathway"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}