Prisma Datamodel: Primary key as a combination of two relational models

Question

I have a problem in Prisma data modeling where I have to constrain that a user can submit only one review for a product. I have following design for the non-constrained situation.

Should Customer and Product be combined into a primary key in ProductReview model, or should this constraint be imposed at the application server level, and not at the database level?

Datamodel for now (non-constrained version):

type Product {
  id: ID! @unique
  title: String!
  reviews: [ProductReview!]! @relation(name: "ProductReviews", onDelete: CASCADE)
}

type Customer {
  id: ID! @unique
  email: String @unique
}

type ProductReview {
  id: ID! @unique
  forProduct: Product! @relation(name: "ProductReviews", onDelete: SET_NULL)
  byCustomer: Customer!
  review: String!
  ratinng: Float!
}

score 12 · Accepted Answer · 2023-10-01 19:24:41Z

Prisma v2 introduced composite primary keys:

https://newreleases.io/project/github/prisma/prisma/release/2.0.0-preview023

An example from that link:

model User {
  firstName String
  lastName  String
  email     String

  @@id([firstName, lastName])
}

So in the given question example, it is now possible to add to ProductReview:

@@id([id, forProduct])

score 2 · Accepted Answer · 2018-11-20 08:31:58Z

I have to constrain that a user can submit only one review for a product. I have following design for the non-constrained situation.

Unfortunately, this is currently not possible with Prisma. There already is an open feature request asking for this functionality, please leave your 👍 on the issue!

To get that functionality in your application, you’ll need to implement that constraint manually on the application layer (e.g. express, apollo-server or graphql-yoga).

You can take a look at this page of How to GraphQL where there’s a similar situation with the User, Link and Vote types. Here’s how the resolver to create a Vote and ensurs no votes from that user already exist is implemented with graphql-yoga:

async function vote(parent, args, context, info) {
  // 1
  const userId = getUserId(context)

  // 2
  const linkExists = await context.db.exists.Vote({
    user: { id: userId },
    link: { id: args.linkId },
  })
  if (linkExists) {
    throw new Error(`Already voted for link: ${args.linkId}`)
  }

  // 3
  return context.db.mutation.createVote(
    {
      data: {
        user: { connect: { id: userId } },
        link: { connect: { id: args.linkId } },
      },
    },
    info,
  )
}

Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks! — Nov 19, 2018 at 13:05

Sanjay SPSanjay SP 1302 silver badges8 bronze badges · Accepted Answer · 2019-07-11 08:36:55Z

There is a workaround. To implement the concept of multiple primary keys like SQL.
The idea is simple, Create one more field called “UniqueCustomerReview” under “ProductReview”. And while on mutation set the “UniqueCustomerReview” value to “[customerEmail]_[productID]”. SO we can now use the default unique of the prisma.

Your data model will look like:

type Product {
id: ID! @unique
  title: String!
  reviews: [ProductReview!]! @relation(name: "ProductReviews", onDelete: CASCADE)
}

type Customer {
  id: ID! @unique
  email: String @unique
}

type ProductReview {
  id: ID! @unique
  forProduct: Product! @relation(name: "ProductReviews", onDelete: SET_NULL)
  byCustomer: Customer!
  review: String!
  ratinng: Float!
  UniqueCustomerReview:String!  # adding a extra field
}

Creating or Mutation query :

mutation{
createProductReview(
data:{
forProduct: {"connect":{"id":"<Replacec_with_product_id>"}}
byCustomer: {"connect":{"email":"<Replacec_with_customer_email>"}}
review: "my product review..."
ratinng: 5.0
UniqueCustomerReview:"[email protected]_<Poductid>" # replace the string with user email and product id. this will create a unique product review for the user alone.
      }
                   )
{
UniqueCustomerReview
# ... any requied fields
}
        }

You should add @unique to the UniqueCustomerReview: String! — Dec 16, 2019 at 15:56

score 1 · Accepted Answer · 2018-11-19 11:00:59Z

I will answer from the MySQL perspective. If you want to enforce that a given customer can only be associated with a given product once, then you should make (cusotmer_id, product_id) a unique key (maybe primary) in the ProductReview table:

ALTER TABLE ProductReview ADD UNIQUE KEY uk_cust_prod (customer_id, product_id);

This means that any attempt to insert a record for a given customer and product, when such a relation already exists, would fail at the database level.

If you also want to add an application level check for this, you of course may do so, and perhaps handle it there first.

Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there? — Nov 19, 2018 at 12:48

unlocounloco 6,9782 gold badges48 silver badges58 bronze badges · Accepted Answer · 2021-06-28 21:49:10Z

1

In my case, it was enough to do a string concatenation on the id

So the id is for example "120-15" for product #120 and customer #15

answered Jun 28, 2021 at 21:49

unloco

6,9782 gold badges48 silver badges58 bronze badges

Prisma Datamodel: Primary key as a combination of two relational models