MemberJunction / MemberJunction/MJ

Soft composite PK in additionalSchemaInfo can leave entity in broken state requiring manual DB fix

Open
#2,568 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

When a composite soft primary key is defined in `additionalSchemaInfo.json` and one of the PK columns shares a name with a regular column in the table, CodeGen generates a stored procedure with the column name appearing twice in the SET/INSERT clause. The stored procedure creation fails, but the entity and its field metadata (with `IsPrimaryKey=1`, `IsSoftPrimaryKey=1`) are already persisted to the database. Subsequent CodeGen runs continue to fail because the metadata is cached — the only fix is manual SQL intervention to clear the flags.

## Steps to Reproduce

1. Define a composite soft PK in `additionalSchemaInfo.json` for a table that has no SQL-defined PK:
```json
{
"hubspot": [
{
"TableName": "Emails",
"PrimaryKey": [
{ "FieldName": "ID" },
{ "FieldName": "RecordID" }
]
}
]
}
```
2. Run CodeGen. The entity is created and field metadata is persisted with both `ID` and `RecordID` marked as `IsPrimaryKey=1` and `IsSoftPrimaryKey=1`.
3. CodeGen generates `spCreateEmails` with `RecordID` appearing twice (once as a PK parameter, once as a regular column), causing:
```
The column name 'RecordID' is specified more than once in the SET clause or column list of an INSERT.
```
4. The stored procedure fails to create. Permissions scripts then fail because the proc doesn't exist.
5. Fix the `additionalSchemaInfo.json` to use a single-column PK (just `ID`).
6. Run CodeGen again — **same error**. The fix to the JSON file has no effect because the entity field metadata already has `RecordID` flagged as `IsPrimaryKey=1` in the `__mj.EntityField` table from step 2.

## Required Manual Fix

The only way to recover is direct SQL:
```sql
UPDATE ef
SET ef.IsPrimaryKey = 0, ef.IsSoftPrimaryKey = 0
FROM __mj.EntityField ef
JOIN __mj.Entity e ON ef.EntityID = e.ID
WHERE e.SchemaName = 'hubspot' AND e.BaseTable = 'Emails'
AND ef.Name = 'RecordID';
```

## Expected Behavior

Two possible fixes (not mutually exclusive):

1. **Prevention**: When generating stored procedures for entities with soft PKs, CodeGen should detect that a PK column name collides with a regular column name and either deduplicate or raise a clear error before persisting metadata.

2. **Self-healing**: When `additionalSchemaInfo.json` is updated to remove a column from a soft PK, CodeGen should detect the discrepancy between the JSON config and the persisted metadata and clear the `IsPrimaryKey`/`IsSoftPrimaryKey` flags automatically on the next run — rather than requiring manual SQL intervention.

## Environment

- MJ CLI: 5.33.0
- Node: v24.11.1
- Platform: darwin-arm64
- Database: SQL Server (Azure SQL / Docker)

## Additional Context

This was discovered while onboarding a new client database (`hubspot.Emails` table with no SQL-defined PK). The composite soft PK `[ID, RecordID]` was generated by the DB Auto Doc tool's relationship discovery. The issue is specifically that the metadata persistence is a one-way operation — once the soft PK flags are written, there's no mechanism to retract them via config changes alone.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.