NestJS+Prisma项目upsert酒店报错：Room_hotelCode_key唯一约束冲突

我正在开发基于Prisma的NestJS应用，编写了一个服务接收CreateHotelDTO，通过upsert方法查找酒店，若不存在则创建。多次修改schema和查询语句后仍无法成功运行，报错信息显示Room_hotelCode_key唯一约束冲突。

Prisma Schema #

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}
model Hotel{
    id Int @id @default(autoincrement())
    hotelCode String @unique
    rooms Room[]
  }
model Room{
    id Int @id @default(autoincrement())
    roomName String @unique
    status RoomStatus @default(BOOKED)
    hotel Hotel @relation(fields: [hotelCode], references: [hotelCode])
    hotelCode String @unique
  }
enum RoomStatus { 
  BOOKED 
  CHECKIN 
  CHECKOUT
}

Service代码（hotels.service.ts） #

import { Injectable } from '@nestjs/common';
import { PrismaService } from 'src/orm/prisma.service';
import { CreateHotelDTO } from './dtos/create-hotel.dto';

@Injectable()
export class HotelsService {
  constructor(private prisma: PrismaService) {}

  async createHotel(createHotelDTO: CreateHotelDTO): Promise<any> {
    const hotel = await this.prisma.hotel.upsert({
      where: { hotelCode: createHotelDTO.hotelCode },
      update: {},
      create: {
        hotelCode: createHotelDTO.hotelCode,
        rooms: {
          createMany: {
            data: createHotelDTO.rooms,
          },
        },
      },
    });
    return hotel;
  }
}

Postman请求体 #

{
    "hotelCode": "CITY",
    "rooms": [
        {
            "roomName":"201",
            "status": "BOOKED"
        },
        {
            "roomName":"202",
            "status": "BOOKED"
        },
        {
            "roomName":"203",
            "status": "BOOKED"
        }
    ]
}

完整错误日志 #

Invalid `this.prisma.hotel.upsert()` invocation in
src\features\hotels\hotels.service.ts:10:43

   7 constructor(private prisma: PrismaService) {}
   8 
   9 async createHotel(createHotelDTO: CreateHotelDTO): Promise<any> {
→ 10   const hotel = await this.prisma.hotel.upsert(
Unique constraint failed on the constraint: `Room_hotelCode_key`
PrismaClientKnownRequestError:
Invalid `this.prisma.hotel.upsert()` invocation in
src\features\hotels\hotels.service.ts:10:43

   7 constructor(private prisma: PrismaService) {}
   8
   9 async createHotel(createHotelDTO: CreateHotelDTO): Promise<any> {
→ 10   const hotel = await this.prisma.hotel.upsert(
Unique constraint failed on the constraint: `Room_hotelCode_key`
...

问题出在Room模型的hotelCode字段被标记为@unique，这意味着数据库中所有Room的hotelCode必须唯一，也就是一个酒店只能对应一个房间，完全不符合业务逻辑——一个酒店必然对应多个房间。

当你通过createMany创建多个Room时，它们的hotelCode都是同一个值（比如请求里的"CITY"），第二个Room插入时就会触发Room_hotelCode_key的唯一约束冲突。

修改Prisma Schema中的Room模型，移除hotelCode的@unique约束：

model Room{
    id Int @id @default(autoincrement())
    roomName String @unique
    status RoomStatus @default(BOOKED)
    hotel Hotel @relation(fields: [hotelCode], references: [hotelCode])
    hotelCode String // 去掉@unique约束
  }

修改后，执行prisma migrate dev更新数据库结构，再运行服务即可正常创建带多个房间的酒店。

内容的提问来源于stack exchange，提问作者manaclan