{"record":{"id":"e8b84974ece30be3","repo":"pathwaycom/pathway","slug":"pw-io-mssql-read-primary-key-column-s-nullable-p","errorCode":null,"errorMessage":"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.","messagePattern":"pw\\.io\\.mssql\\.read primary_key column\\(s\\) (.+?) 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\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/mssql/__init__.py","lineNumber":232,"sourceCode":"    _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\"\n\n    data_storage = api.DataStorage(\n        storage_type=\"mssql\",\n        connection_string=connection_string,\n        table_name=table_name,\n        schema_name=schema_name,\n        mode=(\n            api.ConnectorMode.STATIC if not cdc_enabled else api.ConnectorMode.STREAMING\n        ),\n    )\n    data_format = api.DataFormat(","sourceCodeStart":214,"sourceCodeEnd":250,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/mssql/__init__.py#L214-L250","documentation":"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.","triggerScenarios":"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.","commonSituations":"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'.","solutions":["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.","If the column genuinely carries NULLs in the source, it is not a real primary key — choose a different key column or combination.","Regenerate the schema from DDL with nullable flags honored only for non-key columns."],"exampleFix":"# before\nclass MySchema(pw.Schema):\n    id: int | None = pw.column_definition(primary_key=True)\n    name: str\n\n# after\nclass MySchema(pw.Schema):\n    id: int = pw.column_definition(primary_key=True)\n    name: str | None","handlingStrategy":"validation","validationCode":"def nullable_pk_columns(schema) -> list[str]:\n    pks = schema.primary_key_columns()\n    dtypes = schema._dtypes()\n    import pathway as pw\n    return [c for c in pks if isinstance(dtypes[c], pw.dt.Optional)]\n\nbad = nullable_pk_columns(MySchema)\nassert not bad, f\"Nullable primary-key columns: {bad}\"","typeGuard":"import pathway as pw\n\ndef has_non_nullable_pks(schema) -> bool:\n    dtypes = schema._dtypes()\n    return all(\n        not isinstance(dtypes[c], pw.dt.Optional)\n        for c in schema.primary_key_columns()\n    )","tryCatchPattern":"try:\n    table = pw.io.mssql.read(conn, table_name, schema=MySchema)\nexcept ValueError as e:\n    if \"nullable\" in str(e) and \"primary_key\" in str(e):\n        raise SystemExit(\"Annotate PK columns as non-Optional in the Pathway schema\") from e\n    raise","preventionTips":["Reserve Optional annotations for columns the database actually allows to be NULL.","Database primary keys are NOT NULL by definition — reflect that in Pathway schemas.","Review generated schemas for defensive | None annotations before use in CDC connectors."],"tags":["mssql","primary-key","nullable","schema","cdc","pathway"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}