MemberJunction / MemberJunction/MJ
CodeGen/DBAutoDoc: composite FKs decomposed into single-column soft FKs with non-unique targets (silent row multiplication)
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
MJ models a foreign key as a single column (`EntityField.RelatedEntityFieldName`), so a composite FK is decomposed into N independent single-column soft FKs, each pointing at its positional partner. When a partner column is not unique on its own, the generated base view **multiplies rows**.
This is engine-independent and distinct from the PostgreSQL extraction bug filed separately. It remains after that bug is fixed.
## Cause
- `DBAutoDoc/src/database/Database.ts:93-98` drops `constraintName` when converting FKs to `dependsOn`, so the grouping is lost before the file is written.
- `AdditionalSchemaInfoGenerator.collectIntrospectedFKs` then writes one standalone FK per column pair.
- CodeGen matches `vwForeignKeys` rows to fields column by column, ignoring `FK_NAME` (`SQLServerCodeGenProvider.ts:1966`, `PostgreSQLCodeGenProvider.ts:2830`). Both views pair columns correctly; the grouping is simply not used.
## Why positional decomposition is wrong
For `FOREIGN KEY (a_key, a_prd_key, a_ptp_key) REFERENCES price (prc_key, prc_prd_key, prc_prd_ptp_key)`, only the first component is a valid standalone FK. The others reference columns that are not unique in `price`. Measured on a production dataset:
| Positional pair | Distinct values in target | Max fan-out |
|---|---:|---:|
| `a_key -> price.prc_key` | 2,404 of 2,404 | 1 (correct) |
| `a_prd_key -> price.prc_prd_key` | 2,175 of 2,404 | 5 |
| `a_ptp_key -> price.prc_prd_ptp_key` | 14 of 2,404 | 689 |
The trailing columns are denormalised copies of the parent's own foreign keys. Their real referents are other tables (`product.prd_key`, `product_type.ptp_key`), which do match 100% with no fan-out.
So the safe decomposition is not positional: it is "keep the component whose target is unique on its own; resolve the rest to their real home table, or omit them".
## Impact
If a component lands on a NOT NULL column whose related entity has a name field, CodeGen emits an INNER label join on a non-unique target and the view silently inflates. An inflated total looks plausible, so it is worse than an empty view.
## Suggested fix
1. Preserve `constraintName` through DBAutoDoc so composite FKs stay identifiable.
2. When decomposing, emit a soft FK only for components whose target is a PK or single-column unique key; skip the rest rather than guessing.
3. Apply the same rule in CodeGen's own physical-FK mapping, so a composite constraint read from `vwForeignKeys` cannot produce a non-unique single-column join target.
Related: #541 (Support for Composite Foreign Keys) is closed as completed, but the FK -> EntityField mapping still decomposes positionally with no uniqueness test.
---
Found alongside #4531, #4532 and #4533 during a production schema-info audit.
Contributor guide
Research direction
Trace constraint handling from DBAutoDoc/src/database/Database.ts:93-98 through AdditionalSchemaInfoGenerator.collectIntrospectedFKs, then inspect SQLServerCodeGenProvider.ts:1966 and PostgreSQLCodeGenProvider.ts:2830. Verify composite constraints remain identifiable and that generated mappings do not create joins against non-unique targets or multiply rows.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql, typescript
- Domain
- backend-api-design, databases, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100