{"record":{"id":"90a3ef5b696033dc","repo":"pathwaycom/pathway","slug":"primary-key-column-pkey-name-r-is-declared-nulla-90a3ef","errorCode":null,"errorMessage":"primary_key column {pkey.name!r} is declared nullable ({pkey._column.dtype}); primary-key columns must be non-nullable in snapshot mode.","messagePattern":"primary_key column (.+?) is declared nullable \\((.+?)\\); primary-key columns must be non-nullable in snapshot mode\\.","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/sqlite/__init__.py","lineNumber":387,"sourceCode":"        if duplicates:\n            raise ValueError(\n                f\"primary_key contains duplicate column(s) {sorted(duplicates)}. \"\n                \"Each column may appear at most once.\"\n            )\n        key_field_names = []\n        for pkey in primary_key:\n            # Raises ValueError when `pkey` belongs to a different table\n            # or does not name a column of `table`, so users get a clear\n            # message at write() time instead of an opaque runtime error.\n            get_column_index(table, pkey)\n            # Reject nullable primary-key columns. SQLite's\n            # INTEGER PRIMARY KEY auto-assigns a rowid for NULL values,\n            # which can silently collide with a later UPSERT and\n            # overwrite unrelated rows; other PK types let NULLs through\n            # but then DELETE ... WHERE pk = NULL never matches on\n            # retractions. Neither case is what the user asked for.\n            if isinstance(pkey._column.dtype, dt.Optional):\n                raise ValueError(\n                    f\"primary_key column {pkey.name!r} is declared nullable \"\n                    f\"({pkey._column.dtype}); primary-key columns must be \"\n                    \"non-nullable in snapshot mode.\"\n                )\n            key_field_names.append(pkey.name)\n\n    data_storage = api.DataStorage(\n        storage_type=\"sqlite\",\n        path=path_str,\n        table_name=table_name,\n        table_writer_init_mode=init_mode_from_str(init_mode),\n        max_batch_size=max_batch_size,\n        snapshot_maintenance_on_output=is_snapshot_mode,\n    )\n    data_format = api.DataFormat(\n        format_type=\"identity\",\n        key_field_names=key_field_names,\n        value_fields=value_fields,","sourceCodeStart":369,"sourceCodeEnd":405,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/sqlite/__init__.py#L369-L405","documentation":"Snapshot mode turns Pathway retractions into DELETE ... WHERE pk = ?, so a NULL primary-key value either never matches (plain PK types: NULL = NULL is not true in SQL) or, for SQLite INTEGER PRIMARY KEY, silently auto-assigns a rowid that can collide with a later UPSERT and overwrite an unrelated row. Because either behavior silently corrupts the destination table, write() rejects primary-key columns whose dtype is Optional (nullable) at call time.","triggerScenarios":"Calling pw.io.sqlite.write(..., output_table_type=\"snapshot\", primary_key=[...]) where any referenced column is typed Optional[T] — e.g. a schema field declared as `id: int | None` or made nullable via table.with_columns(id=pw.this.id + 0, id=pw.apply(optional_fn)) — i.e. dtype is dt.Optional.","commonSituations":"Schemas inferred from JSON/CSV where the key field is sometimes missing; Python-style annotations like `user_id: int | None = None` in a pw.Schema; upstream transformations that introduce nullability on the key column.","solutions":["Make the key column non-nullable in the schema (change `id: int | None` to `id: int`) if the data is in fact always present.","Filter out rows with null keys before writing: t = t.filter(t.id.is_not_none()), then use primary_key=[t.id].","Choose a different primary-key column that is guaranteed non-null (e.g. an explicit pw.this.id surrogate key)."],"exampleFix":"# before\nclass Input(pw.Schema):\n    owner: str | None = None\n    pet: str\npw.io.sqlite.write(t, \"db\", \"s\", output_table_type=\"snapshot\", primary_key=[t.owner, t.pet])\n\n# after\nt = t.filter(pw.this.owner.is_not_none())\npw.io.sqlite.write(t, \"db\", \"s\", output_table_type=\"snapshot\", primary_key=[t.owner, t.pet])","handlingStrategy":"validation","validationCode":"import pathway as pw\nfrom pathway import dt\n\ndef check_pk_not_nullable(table, primary_key):\n    schema = table.schema\n    for col in primary_key:\n        dtype = schema[col.name].dtype\n        if isinstance(dtype, dt.Optional):\n            raise ValueError(f\"primary key column {col.name} is nullable\")\n    return True","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Declare key columns as non-Optional in the pw.Schema (id: int, not int | None) when semantics guarantee presence.","Filter null keys early: t = t.filter(t.id.is_not_none()) before the write step, and log dropped counts."],"tags":["pathway","sqlite","primary-key","nullable","data-integrity"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}