pathwaycom/pathway · error · ValueError

pw.io.mssql.read primary_key column(s) {nullable_pks} are de

Error message

pw.io.mssql.read primary_key column(s) {nullable_pks} are declared nullable; primary-key columns must be non-nullable so the connector can derive a unique row identity. NULL values would collide on the same Pathway key and CDC tracking would silently merge unrelated rows.

What it means

Pathway's MSSQL connector rejects schemas where a primary-key column is Optional (nullable). The connector derives each row's Pathway key from the primary-key columns; NULL key parts would collapse distinct rows onto the same key, silently merging unrelated rows during CDC tracking. This ValueError fires at read() time listing the offending nullable column names.

Source

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

    _validate_identifier("table_name", table_name)
    _validate_identifier("schema_name", schema_name)

    schema, api_schema = read_schema(schema)

    primary_key_columns = schema.primary_key_columns()
    if not primary_key_columns:
        raise ValueError(
            "pw.io.mssql.read requires at least one primary key column in the schema. "
            "Mark the column(s) that form the table's primary key with "
            "pw.column_definition(primary_key=True)."
        )

    pk_dtypes = schema._dtypes()
    nullable_pks = [
        name for name in primary_key_columns if isinstance(pk_dtypes[name], dt.Optional)
    ]
    if nullable_pks:
        raise ValueError(
            f"pw.io.mssql.read primary_key column(s) {nullable_pks} are declared "
            "nullable; primary-key columns must be non-nullable so the connector "
            "can derive a unique row identity. NULL values would collide on the "
            "same Pathway key and CDC tracking would silently merge unrelated rows."
        )

    cdc_enabled = mode == "streaming"

    data_storage = api.DataStorage(
        storage_type="mssql",
        connection_string=connection_string,
        table_name=table_name,
        schema_name=schema_name,
        mode=(
            api.ConnectorMode.STATIC if not cdc_enabled else api.ConnectorMode.STREAMING
        ),
    )
    data_format = api.DataFormat(

View on GitHub (pinned to fa2f74a464)

Solutions

1. Change the primary-key column's annotation from Optional[int] to int (or str) in the schema, since a SQL Server primary key is non-nullable by definition.
2. If the column genuinely carries NULLs in the source, it is not a real primary key — choose a different key column or combination.
3. Regenerate the schema from DDL with nullable flags honored only for non-key columns.

Example fix

# before
class MySchema(pw.Schema):
    id: int | None = pw.column_definition(primary_key=True)
    name: str

# after
class MySchema(pw.Schema):
    id: int = pw.column_definition(primary_key=True)
    name: str | None

def nullable_pk_columns(schema) -> list[str]:
    pks = schema.primary_key_columns()
    dtypes = schema._dtypes()
    import pathway as pw
    return [c for c in pks if isinstance(dtypes[c], pw.dt.Optional)]

bad = nullable_pk_columns(MySchema)
assert not bad, f"Nullable primary-key columns: {bad}"

import pathway as pw

def has_non_nullable_pks(schema) -> bool:
    dtypes = schema._dtypes()
    return all(
        not isinstance(dtypes[c], pw.dt.Optional)
        for c in schema.primary_key_columns()
    )

try:
    table = pw.io.mssql.read(conn, table_name, schema=MySchema)
except ValueError as e:
    if "nullable" in str(e) and "primary_key" in str(e):
        raise SystemExit("Annotate PK columns as non-Optional in the Pathway schema") from e
    raise

When it happens

Trigger: Declaring a primary-key column as Optional, e.g. id: int | None = pw.column_definition(primary_key=True), or using Optional[int] typing for a key column in the schema passed to pw.io.mssql.read.

Common situations: Generating the Pathway schema from a database introspection tool that marks all columns nullable; migrating from a schema written for a connector that tolerated optional keys; annotating columns with | None defensively 'just in case'.

Related errors

• pw.io.mssql.read requires at least one primary key column in
• primary_key column {pkey.name!r} is declared nullable ({pkey
• pw.io.mysql.read requires at least one primary key column in
• pw.io.mysql.read primary_key column(s) {nullable_pks} are de
• primary_key column {pkey.name!r} is declared nullable ({pkey

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