{"record":{"id":"e7d4188ecf3eb005","repo":"pathwaycom/pathway","slug":"pw-io-mssql-read-requires-at-least-one-primary-key","errorCode":null,"errorMessage":"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).","messagePattern":"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\\)\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/mssql/__init__.py","lineNumber":221,"sourceCode":"    ...     connection_string=\"Server=tcp:localhost,1433;Database=testdb;\"\n    ...         \"User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=true\",\n    ...     table_name=\"my_table\",\n    ...     schema=MySchema,\n    ...     mode=\"static\",\n    ... )\n    >>> pw.io.jsonlines.write(table, \"output.jsonl\")  # doctest: +SKIP\n    >>> pw.run(persistence_config=persistence_config)  # doctest: +SKIP\n    \"\"\"\n    _check_entitlements(\"mssql\")\n\n    _validate_identifier(\"table_name\", table_name)\n    _validate_identifier(\"schema_name\", schema_name)\n\n    schema, api_schema = read_schema(schema)\n\n    primary_key_columns = schema.primary_key_columns()\n    if not primary_key_columns:\n        raise ValueError(\n            \"pw.io.mssql.read requires at least one primary key column in the schema. \"\n            \"Mark the column(s) that form the table's primary key with \"\n            \"pw.column_definition(primary_key=True).\"\n        )\n\n    pk_dtypes = schema._dtypes()\n    nullable_pks = [\n        name for name in primary_key_columns if isinstance(pk_dtypes[name], dt.Optional)\n    ]\n    if nullable_pks:\n        raise ValueError(\n            f\"pw.io.mssql.read primary_key column(s) {nullable_pks} are declared \"\n            \"nullable; primary-key columns must be non-nullable so the connector \"\n            \"can derive a unique row identity. NULL values would collide on the \"\n            \"same Pathway key and CDC tracking would silently merge unrelated rows.\"\n        )\n\n    cdc_enabled = mode == \"streaming\"","sourceCodeStart":203,"sourceCodeEnd":239,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/mssql/__init__.py#L203-L239","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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)."],"exampleFix":"# before\nclass MySchema(pw.Schema):\n    id: int\n    name: str\n\n# after\nclass MySchema(pw.Schema):\n    id: int = pw.column_definition(primary_key=True)\n    name: str","handlingStrategy":"validation","validationCode":"def schema_has_primary_key(schema: type[pw.Schema]) -> bool:\n    return len(schema.primary_key_columns()) > 0\n\nassert schema_has_primary_key(MySchema), \\\n    \"MSSQL source schema needs primary_key=True on key column(s)\"","typeGuard":"from pathway.engine import Schema\n\ndef is_keyed_schema(schema: type[Schema]) -> bool:\n    try:\n        return bool(schema.primary_key_columns())\n    except Exception:\n        return False","tryCatchPattern":"try:\n    table = pw.io.mssql.read(conn, table_name, schema=MySchema)\nexcept ValueError as e:\n    if \"primary key column\" in str(e):\n        raise SystemExit(\"Fix schema: mark the table's PK with pw.column_definition(primary_key=True)\") from e\n    raise","preventionTips":["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."],"tags":["mssql","primary-key","schema","cdc","pathway"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}