MemberJunction / MemberJunction/MJ
Soft composite PK in additionalSchemaInfo can leave entity in broken state requiring manual DB fix
- 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
Assessment
This issue has not been assessed yet.