Question 90439 in PostgreSQL Question posted in
The official documentation can be found here.

Prisma Model with unique one to many – Postgresql

A Product can have various sizes so I added one to many relation. But Each product can have only 1 unique size, i.e, in many to many the sizes should not repeat.
For instance, Product X can have size 1, 2, 3 but if user tries to add size 2 which already exists, it should fail (and it does) but now the issue is when Product Y is added, adding size 1 fails because it was unique but It should be unique per variant not overall. Any way to do this while modelling the DB or I have to add a manual check and throw error if the size of the same variant already exists.

Database – Postgresql
Using Prisma

Tags:

2

Answers


  1. 0

    You can create unique indexes using multiple fields in postgres. https://www.postgresqltutorial.com/postgresql-indexes/postgresql-unique-index/ scroll down a bit for the multiple fields section.

    Without knowing your existing table structure I can’t say with confidence that this code would work but you’ll want something like this.

    CREATE UNIQUE INDEX idx_products_size ON products(id, size);
    Login or Signup to reply.
  2. 0

    In Prisma, you can model your relations like this:

    model Product {
  id          String        @id @default(uuid())
  name        String
  description String
  sizes       ProductSize[]
}

model ProductSize {
  id          String  @id @default(uuid())
  name        String
  description String
  product     Product @relation(fields: [productId], references: [id])
  productId   String

  @@unique([productId, name])
}

    This contains a unique index over a combination of two fields (productId and name). So for each product there can only be a unique named size.
    Example – (productId – A, Size – 1), (productId – A, Size – 2). Adding another record with (productId – A, Size – 1) would throw an error but (productId – B, Size – 1) will be allowed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search