{"record":{"id":"dd42f0ad81c11735","repo":"cocoindex-io/cocoindex","slug":"vec0-virtual-tables-require-integer-primary-key-g","errorCode":null,"errorMessage":"vec0 virtual tables require INTEGER primary key, got {pk_col_type} for column '{pk_col_name}'","messagePattern":"vec0 virtual tables require INTEGER primary key, got (.+?) for column '(.+?)'","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/cocoindex/connectors/sqlite/_target.py","lineNumber":1154,"sourceCode":"        raise ValueError(\n            \"vec0 virtual tables require at least one float[N] vector column\"\n        )\n    all_cols = set(table_schema.columns.keys())\n    invalid_partition = set(virtual_table_def.partition_key_columns) - all_cols\n    if invalid_partition:\n        raise ValueError(f\"Partition key columns not in schema: {invalid_partition}\")\n    invalid_aux = set(virtual_table_def.auxiliary_columns) - all_cols\n    if invalid_aux:\n        raise ValueError(f\"Auxiliary columns not in schema: {invalid_aux}\")\n    if len(table_schema.primary_key) != 1:\n        raise ValueError(\n            f\"vec0 virtual tables require exactly one primary key column, \"\n            f\"got {len(table_schema.primary_key)}: {table_schema.primary_key}\"\n        )\n    pk_col_name = table_schema.primary_key[0]\n    pk_col_type = table_schema.columns[pk_col_name].type\n    if pk_col_type != \"INTEGER\":\n        raise ValueError(\n            f\"vec0 virtual tables require INTEGER primary key, \"\n            f\"got {pk_col_type} for column '{pk_col_name}'\"\n        )\n\n\ndef table_target(\n    db: ContextKey[ManagedConnection],\n    table_name: str,\n    table_schema: TableSchema[RowT],\n    *,\n    managed_by: target.ManagedBy = target.ManagedBy.SYSTEM,\n    virtual_table_def: Vec0TableDef | None = None,\n) -> \"coco.TargetState[_RowHandler]\":\n    \"\"\"\n    Create a TargetState for a SQLite table target.\n\n    Use with ``coco.mount_target()`` to mount and get a child provider,\n    or with ``declare_table_target()`` / ``mount_table_target()`` for convenience wrappers.","sourceCodeStart":1136,"sourceCodeEnd":1172,"githubUrl":"https://github.com/cocoindex-io/cocoindex/blob/e84aa99b3292c5270a4b313b2a7137ad9ce8ab3b/python/cocoindex/connectors/sqlite/_target.py#L1136-L1172","documentation":"vec0 virtual tables require the single primary key column to be of SQLite type INTEGER (rowid-style). The primary key column's declared type is something else (TEXT, BLOB, REAL, etc.), so CocoIndex rejects it during vec0 validation.","triggerScenarios":"Calling table_target() for a vec0 table whose single primary key column has a non-INTEGER type, e.g. primary_key=[\"id\"] with id declared as TEXT/str (UUID strings) or REAL (float ids).","commonSituations":"Using UUID or string identifiers as primary keys, a habit from other databases; declaring the id column without a type so it defaults to non-INTEGER; migrating a schema from Postgres where SERIAL/BIGINT mapped to a non-INTEGER SQLite name.","solutions":["Declare the primary key column as INTEGER (int in Python) in the schema.","If you need string/UUID keys, make an INTEGER auto-increment id the primary key and store the UUID in a separate column.","Check the column type mapping used when building the schema and ensure the PK maps to SQLite 'INTEGER'."],"exampleFix":"// before\nschema = table_schema(columns={\"id\": \"TEXT\", \"embedding\": \"float[768]\"}, primary_key=[\"id\"])\n// after\nschema = table_schema(columns={\"id\": \"INTEGER\", \"doc_uuid\": \"TEXT\", \"embedding\": \"float[768]\"}, primary_key=[\"id\"])","handlingStrategy":"type-guard","validationCode":"pk = schema.primary_key[0]\nif schema.columns[pk].type != \"INTEGER\":\n    raise ValueError(\"vec0 primary key must be INTEGER\")","typeGuard":"def pk_is_integer(schema) -> bool:\n    pk = schema.primary_key[0]\n    return schema.columns[pk].type == \"INTEGER\"","tryCatchPattern":"try:\n    target = table_target(schema, virtual_table_def)\nexcept ValueError as e:\n    if \"INTEGER primary key\" in str(e):\n        # switch PK column to INTEGER or move string key to a separate column\n        ...","preventionTips":["Always use int/INTEGER for vec0 primary keys; store UUIDs in a separate TEXT column.","Verify type mappings when building schemas for SQLite.","Add a schema linter/test that asserts PK type before creating vec0 tables."],"tags":["sqlite","vec0","primary-key","type-mismatch"],"backgroundTag":"type-mismatch","analyzedSha":"e84aa99b3292c5270a4b313b2a7137ad9ce8ab3b","analyzedAt":"2026-09-08T15:59:19.997Z","contentChangedAt":"2026-09-08T15:59:19.997Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}