MemberJunction / MemberJunction/MJ

CodeGen/DBAutoDoc: composite FKs decomposed into single-column soft FKs with non-unique targets (silent row multiplication)

Open
#4,534 0 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.