Data Specification SQL - Missing Primary Key

[NigelPalmer]

Description

I am using OUH Data Warehouse module and have generated a query that is missing a primary key. Note that foreign keys are not setup in this Data Model and could be the cause of the issue. We need to address this somehow to avoid generating corrupt SQL.

IF OBJECT_ID(N'tempdb..#cohort') IS NOT NULL
    DROP TABLE [#cohort]
GO

CREATE TABLE [#cohort] ( // This should have a PERSON_ID field
)
GO

INSERT INTO [#cohort] (
)
SELECT // This should have [Inpatient].[INP_GENERAL].[PERSON_ID]

FROM
    [Inpatient].[INP_GENERAL]
WHERE
    [Inpatient].[INP_GENERAL].[PERSON_ID] > '1'
    AND (
        [EDEscalation].[BREAKDOWN].[Stream] < '2024-09-21T15:45'
    )
GO

SELECT
    [EDEscalation].[BREAKDOWN].[RUNTIME],
    ...
FROM
    [EDEscalation].[BREAKDOWN] // Note, no join here because lack of foreign keys. That is correct.
GO

SELECT
    [Inpatient].[INP_GENERAL].[PERSON_ID]
FROM
    [Inpatient].[INP_GENERAL] // I would expect this to join to the cohort query though.
GO

IF OBJECT_ID(N'tempdb..#cohort') IS NOT NULL
    DROP TABLE [#cohort]
GO

Steps to reproduce

e.g.

1. Use the OUH Data Warehouse - modules database
2. Set the core table to be: Inpatient.INP_GENERAL
3. Set the element PATIENT_ID in [Inpatient].[INP_GENERAL] to be a primary key.
4. Edit the mdm-plugin-explorer "id" field to be "PATIENT_ID" and rebuild mdm-plugin-explorer. (See: Retrieve primary key field using profiles in getRequiredCoreTableElementIds endpoint MauroDataMapper-Plugins/mdm-plugin-explorer#54 This is a workaround until that is fixed)
5. In the MDE select [EDEscalation].[BREAKDOWN] and add it to a new Data Specification.
6. Create a cohort query as per the where clause in the cohort query above
7. Finalise and submit the query
8. Check the generated SQL.

Expected behaviour

The query should be created successfully