MemberJunction / MemberJunction/MJ

vwTableUniqueKeys marks every column of a composite unique index as IsUnique

Open
#4,536 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.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

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.