feat(inspector): text primary keys collide and cannot be referenced — emit serial for varchar/char keys

Open
  • Is something wrong?
#441 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 4 days

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
45/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
java, postgresql
Domain
database

Research direction

Start in the schema inspector's handling of varchar and char primary keys, where they currently become random char[1..n] values, and in the ref[...] generation for foreign keys that point at them. Check how the database destination binds integers into varchar columns. Done when a text PK inspects as serial with the review comment, a child FK resolves against the parent, and a Northwind-style schema loads without duplicate or missing-key errors. Composite and non-key text columns must stay unchanged.

Written by the indexing model from the issue text.

Description

enhancement

Summary

A primary key on a text column (varchar(n) / char(n)) is inspected as random char[1..n] with # UNIQUE/PRIMARY KEY not enforced — values may collide. Two consequences:

  1. The parent table collides on its own PK — with char[1..5] many values are 1–2 chars long, so duplicates come within the first batch:
    ERROR: duplicate key value violates unique constraint "pk_customer_demographics" — Key (customer_type_id)=(L) already exists
  2. Children can never match it — the FK side is inspected as ref[customers.customer_id, 1..count], which emits integers, while the parent holds random strings:
    ERROR: ... violates foreign key constraint "fk_orders_customers" — Key (customer_id)=(33) is not present in table "customers"

Text keys are common (Northwind: customers.customer_id varchar(5), territories.territory_id varchar(20), customer_demographics.customer_type_id varchar(5); ISO codes, SKUs, legacy schemas), and this breaks every FK pointing at them.

Observation

The tools already exist: the database destination binds an integer into a varchar key column without complaint (PostgreSQL assignment cast — verified with serial into varchar(5)), and ref[parent.key, 1..N] over a serial parent produces matching values. So a text PK inspected as serial (or unique[...] for a non-dense key) gives a collision-free parent and FK children that resolve, with no new type.

Suggested fix

  • Text PK / UNIQUE column of width n → serial (dense) with a review comment: # text key generated as numeric strings — max 10^n − 1 rows (ties into the capacity work in #440).
  • Leave composite keys with text parts, and non-key text columns, as they are today.
  • Optional, later: a formatted string key (prefix / zero-padding) so values look like the source system's (ALFKI-style codes need a deterministic string permutation; out of scope here).

Workaround

Hand-edit the text PK fields to serial. Keep the parent's row count within the column width (varchar(5) → ≤ 99 999 rows).

Related: #440.

Update (2026-11-04) — verified on 4 vendors

Throw-away probe through the real JdbcTypeMapper (setLong):

PostgreSQL 16 MySQL 8.4 SQL Server 2022 Oracle Free
integer into VARCHAR(5) PK, text FK child OK OK OK OK
integer into CHAR(5) PK (padded) + FK OK OK OK OK
123456 into VARCHAR(5) fails fails fails fails

So serial for text keys needs no new type, but it needs the width ceiling (serial[1..10^n − 1], see #440 part 2). The same text keys appear in every vendor's Northwind (nchar(5) / varchar(5) / VARCHAR2(5) CustomerID). For UNIQUE text columns with a semantic hint (e.g. email), see #455.
Phase B (optional): formatted string keys (C00042) so JSON/Avro keep string types — needs the parent-aware references of #442.

Dominant language
Java
Stars
3
Forks
4
Avg merge
8h 10m
Merged PRs (30d)
9

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from mferretti/SeedStream

All issues in mferretti/SeedStream

Similar issues

More Java issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.