Error when generating GraphQL schema from schema.prisma when referencing alternative/unique key.

[ricklrq]

I have (simplified) table design in SQL Server as follows:

CREATE TABLE [Customer].[Customer]
(
    [CustomerId] INT NOT NULL IDENTITY(1, 1), 
        CONSTRAINT [PK_Customer_CustomerId] PRIMARY KEY ([CustomerId]),
    [LastName] VARCHAR(50) NOT NULL,
    [FirstName] VARCHAR(50) NOT NULL,
    [MiddleName] VARCHAR(50) NULL,
    [BirthDate] DATE NULL
) 

CREATE TABLE [Referral].[Referral]
(
    [ReferralId] INT NOT NULL IDENTITY(1, 1),
        CONSTRAINT [PK_Referral_ReferralId] PRIMARY KEY ([ReferralId]),
    [CustomerId] INT NOT NULL,
        CONSTRAINT [FK_Referral_Customer_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [Customer].[Customer]([CustomerId]),
        CONSTRAINT [AK_Referral_CustomerId_ReferralId] UNIQUE ([CustomerId], [ReferralId]), 
    [ReferralDate] DATE NOT NULL
)

CREATE TABLE [DentalService].[DentalService]
(
    [DentalServiceId] INT NOT NULL,
        CONSTRAINT [PK_DentalService_DentalServiceId] PRIMARY KEY ([DentalServiceId]),
    [CustomerId] INT NOT NULL,
        CONSTRAINT [FK_DentalService_Referral_CustomerId_ReferralId] FOREIGN KEY ([CustomerId], [DentalServiceId]) REFERENCES [Referral].[Referral]([CustomerId], [ReferralId]),
    [ServiceDate] DATE NULL,
    -- more columns below
) 

I know it might not be a good design, but it is valid and serves the purpose for my work.

Basically [DentalService].[DentalService] table is like a sub/child/extension table of [Referral].[Referral], though you can say that I should combine the two tables. But dental service is like its own entities. That's why it has its own schema and sets of tables. We have lots of referred services like detox service, car rental service, etc. similar to dental service and all tied to [Referral].[Referral] table.

I tried to generate a Prisma schema, then use nexus-prisma plugin to generate GraphQL schema. I got the following error:

Error: Prisma schema validation - (query-engine-node-api library)
Error code: P1012
error: Error parsing attribute "@relation": A one-to-one relation must use unique fields on the defining side. Either add an `@@unique([CustomerId, DentalServiceId])` attribute to the model, or change the relation to one-to-many.

Can someone explain why I need to add a unique constraint [CustomerId, DentalServiceId] on [DentalService].[DentalService] table? From a database table relationship point of view, I don't think we need to.

[ricklrq]

This is more like a Prisma problem.