MemberJunction / MemberJunction/MJ
vwTableUniqueKeys marks every column of a composite unique index as IsUnique
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
`__mj.vwTableUniqueKeys` unnests every column of a **composite** unique index, so each member column is reported as individually unique and `EntityField.IsUnique` is set to true for all of them.
## Cause
`migrations-pg/v5/V202607181800__v5.49.x__Filter_System_Namespaces_In_Catalog_Views.pg.sql` (and the SQL Server equivalent) unnest `indkey` for every unique, non-primary index without checking `indnkeyatts`. CodeGen then sets `IsUnique` from membership in that view (`PostgreSQLCodeGenProvider.ts:2838-2841, 2877-2881`; `SQLServerCodeGenProvider.ts:1953-1957`).
## Reproduction
A table with `CREATE UNIQUE INDEX ... ON price (prc_key, prc_prd_key, prc_prd_ptp_key)` yields:
```
prc_key IsUnique = true (correct: also the PK)
prc_prd_key IsUnique = true (wrong: 2,175 distinct values over 2,404 rows)
prc_prd_ptp_key IsUnique = true (wrong: 14 distinct values over 2,404 rows)
```
## Impact
`IsUnique` is metadata that both humans and tooling reasonably read as "safe to join on". It contradicted the data by two orders of magnitude here. It is also the natural signal for any future validation of soft-FK targets, so it should be trustworthy before it is used that way.
Two related gaps in the same view: `indkey` includes INCLUDE columns, and partial unique indexes (`indpred IS NOT NULL`) are not excluded, so neither is truly a uniqueness guarantee.
## Suggested fix
Report a column as unique only when it is the sole key column of a unique index: filter to `indnkeyatts = 1` (and skip partial indexes). If composite uniqueness is worth exposing, expose it as a separate key-set concept rather than a per-column boolean.
---
Found alongside #4531, #4532 and #4533 during a production schema-info audit.
Contributor guide
Research direction
Start with migrations-pg/v5/V202607181800__v5.49.x__Filter_System_Namespaces_In_Catalog_Views.pg.sql and its SQL Server equivalent, then inspect the IsUnique handling at PostgreSQLCodeGenProvider.ts:2838-2841, 2877-2881 and SQLServerCodeGenProvider.ts:1953-1957. Reproduce the composite-index case and verify the view reports only sole, non-partial unique index columns as unique.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql, typescript
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100