Error generating Prisma client when table referencing alternative/unique key from other table

[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 from my existing database and it works fine. But then when I tried to run

npx prisma generate

I go 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.

Here is the shema.prisma generated:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
  schemas  = ["Customer", "DentalService", "Referral"]
}

model Customer {
  CustomerId Int        @id(map: "PK_Customer_CustomerId") @default(autoincrement())
  LastName   String     @db.VarChar(50)
  FirstName  String     @db.VarChar(50)
  MiddleName String?    @db.VarChar(50)
  BirthDate  DateTime?  @db.Date
  Referral   Referral[]

  @@schema("Customer")
}

model DentalService {
  DentalServiceId Int       @id(map: "PK_DentalService_DentalServiceId")
  CustomerId      Int
  ServiceDate     DateTime? @db.Date
  Referral        Referral  @relation(fields: [CustomerId, DentalServiceId], references: [CustomerId, ReferralId], onUpdate: NoAction, map: "FK_DentalService_Referral_CustomerId_ReferralId")

  @@schema("DentalService")
}

model Referral {
  ReferralId    Int            @id(map: "PK_Referral_ReferralId") @default(autoincrement())
  CustomerId    Int
  ReferralDate  DateTime       @db.Date
  DentalService DentalService?
  Customer      Customer       @relation(fields: [CustomerId], references: [CustomerId], onUpdate: NoAction, map: "FK_Referral_Customer_CustomerId")

  @@unique([CustomerId, ReferralId], map: "AK_Referral_CustomerId_ReferralId")
  @@schema("Referral")
}

[nurul3101]

Hello @ricklrq 👋

This constraint of defining a unique field when using one-to-one relations was introduced in Prisma v4.0.0 as described here.

Prisma does not automatically determine the uniqueness of fields in the relational database. It relies on the definition of the fields in the relational database as well as any constraints or indexes that have been applied to those fields.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!