Multiple relation to the same field prisma postgresql-CodePudding

I want to create this database deisng https://i.stack.imgur.com/2QYip.png

I need to make model of Station and TrackSegment (A track segment ties two stations together.)

This is my prisma schema but it give me this error "Error validating model 'station': the unique index definitions refers to the field code multiple times.

model Station {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name String
  code String @unique
}

model TrackSegment {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  sourceId      String 
  destinationId String
  stationCode Station @relation(fields: [destinationId, sourceId], references: [code, code])
}

I imagine this is how the data will look.

Station: [
{
  name: 'kalisetail'
  code: 'KLS
},
{
  name: 'rogojampi'
  code: 'RGJ
}]

then the TrackSegment is

id: 1
sourceId: KLS
destinationId: RGJ

both sourceId and destinationId refer to the same field(code) of model Station

CodePudding user response：

You will need to add a second Station relation on your TrackSegment field to differentiate between the source the destination.

model Station {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name                String
  code                String         @unique
  sourceSegments      TrackSegment[] @relation("source")
  destinationSegments TrackSegment[] @relation("destination")
}

model TrackSegment {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  sourceId               String
  destinationId          String
  sourceStationCode      Station @relation(name: "source", fields: [sourceId], references: [code])
  destinationStationCode Station @relation(name: "destination", fields: [destinationId], references: [code])
}

Notice I have always disambiguated the relations by giving them different names.