pathwaycom/pathway · error · ValueError

primary_key column {pkey.name!r} is declared nullable ({pkey

Error message

primary_key column {pkey.name!r} is declared nullable ({pkey._column.dtype}); primary-key columns must be non-nullable in snapshot mode.

What it means

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.

Source

Thrown at python/pathway/io/mssql/__init__.py:473

            names_seen.add(pkey.name)
        if duplicates:
            raise ValueError(
                f"primary_key contains duplicate column(s) {sorted(duplicates)}. "
                "Each column may appear at most once."
            )
        key_field_names = []
        for pkey in primary_key:
            # Raises ValueError when `pkey` belongs to a different table or
            # does not name a column of `table`, so users get a clear
            # message at write() time instead of an opaque runtime error.
            get_column_index(table, pkey)
            # Reject nullable primary-key columns.  SQL Server refuses to
            # build a PRIMARY KEY on a nullable column, and even if the
            # destination table is hand-crafted to allow NULLs, the MERGE
            # statement uses `target.k = source.k` which is UNKNOWN (not
            # TRUE) when both sides are NULL — so retractions never match.
            if isinstance(pkey._column.dtype, dt.Optional):
                raise ValueError(
                    f"primary_key column {pkey.name!r} is declared nullable "
                    f"({pkey._column.dtype}); primary-key columns must be "
                    "non-nullable in snapshot mode."
                )
            key_field_names.append(pkey.name)
    data_format = api.DataFormat(
        format_type="identity",
        key_field_names=key_field_names,
        value_fields=value_fields,
    )

    datasink_type = "snapshot" if is_snapshot_mode else "sink"
    table.to(
        datasink.GenericDataSink(
            data_storage,
            data_format,
            datasink_name=f"mssql.{datasink_type}",
            unique_name=name,

View on GitHub (pinned to fa2f74a464)

Solutions

1. 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.
2. Choose a different key column that is guaranteed non-nullable (e.g. this_row.id or a required column from the source schema).
3. If NULL keys genuinely occur, decide on a sentinel/default value strategy and apply it explicitly before write().

Example fix

# before
pw.io.mssql.write(joined, "t", output_table_type="snapshot", primary_key=[joined.user_id])
# joined.user_id is Optional after a left join

# after
snapshot = joined.with_columns(user_id=pw.coalesce(joined.user_id, -1))
pw.io.mssql.write(snapshot, "t", output_table_type="snapshot", primary_key=[snapshot.user_id])

import pathway as pw

nullable_keys = [c for c in primary_key if isinstance(c._column.dtype, pw.dt.Optional)]
if nullable_keys:
    table = table.with_columns(
        **{c._name: pw.coalesce(table[c._name], -1) for c in nullable_keys}
    )

import pathway as pw

def is_non_nullable(col_ref) -> bool:
    return not isinstance(col_ref._column.dtype, pw.dt.Optional)

try:
    pw.io.mssql.write(t, "t", output_table_type="snapshot", primary_key=[t.user_id])
except ValueError as e:
    if "non-nullable" in str(e):
        t = t.with_columns(user_id=pw.coalesce(t.user_id, -1))
        pw.io.mssql.write(t, "t", output_table_type="snapshot", primary_key=[t.user_id])
    else:
        raise

When it happens

Trigger: 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".

Common situations: 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.

Related errors

• pw.io.mssql.read primary_key column(s) {nullable_pks} are de
• primary_key contains duplicate column(s) {sorted(duplicates)
• primary_key column '{pkey_field.name}' is declared nullable;
• Incorrect S3 path: {s3_path}
• pw.io.mssql.read requires at least one primary key column in

AI-assisted analysis of pathwaycom/pathway@fa2f74a464 (2026-08-15). Data as JSON: /api/errors/1daf95d8ad6615ce. Report an issue: GitHub.