pathwaycom/pathway · error · ValueError

pw.io.mssql.read requires at least one primary key column in

Error message

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).

What it means

Pathway's MSSQL CDC (streaming) connector needs a stable primary key to identify rows across changes, so pw.io.mssql.read requires the Pathway schema to declare at least one primary-key column. Without it, the connector cannot map SQL Server change rows to Pathway table keys. This ValueError is raised at read() call time when schema.primary_key_columns() is empty.

Source

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

    ...     connection_string="Server=tcp:localhost,1433;Database=testdb;"
    ...         "User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=true",
    ...     table_name="my_table",
    ...     schema=MySchema,
    ...     mode="static",
    ... )
    >>> pw.io.jsonlines.write(table, "output.jsonl")  # doctest: +SKIP
    >>> pw.run(persistence_config=persistence_config)  # doctest: +SKIP
    """
    _check_entitlements("mssql")

    _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"

View on GitHub (pinned to fa2f74a464)

Solutions

Mark the column(s) that form the SQL Server table's primary key with pw.column_definition(primary_key=True) in the schema passed to read().
If the source table has a composite key, mark every column of the composite key.
If you only need a one-off snapshot without keys, use a connector that supports keyless reads (e.g. pw.io.mssql in static mode still needs keys, so instead use a generic query/jdbc source appropriate for your version).

Example fix

# before
class MySchema(pw.Schema):
    id: int
    name: str

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

Defensive patterns

Strategy: validation

Validate before calling

def schema_has_primary_key(schema: type[pw.Schema]) -> bool:
    return len(schema.primary_key_columns()) > 0

assert schema_has_primary_key(MySchema), \
    "MSSQL source schema needs primary_key=True on key column(s)"

Type guard

from pathway.engine import Schema

def is_keyed_schema(schema: type[Schema]) -> bool:
    try:
        return bool(schema.primary_key_columns())
    except Exception:
        return False

Try / catch

try:
    table = pw.io.mssql.read(conn, table_name, schema=MySchema)
except ValueError as e:
    if "primary key column" in str(e):
        raise SystemExit("Fix schema: mark the table's PK with pw.column_definition(primary_key=True)") from e
    raise

Prevention

Establish a team convention: every IO schema declares its primary key explicitly.
Write a tiny smoke test per connector schema asserting schema.primary_key_columns() is non-empty.
When generating schemas from DDL, propagate PRIMARY KEY constraints into primary_key=True.

When it happens

Trigger: Calling pw.io.mssql.read(...) with a schema whose column_definitions all use primary_key=False (the default), e.g. class MySchema(pw.Schema): id: int; name: str — no column is marked primary_key=True.

Common situations: Porting a schema from the jsonlines or csv reader where primary keys are optional; assuming pw.Schema auto-detects the primary key; writing the schema quickly from a SQLAlchemy model and dropping the primary-key flag.

Related errors

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