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

prisma unique two fields
prisma 2
prisma io documentation
prisma2 graphql

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!
}

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,
  )
}

Datamodel: Primary key as a combination of two relational models , 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  Prisma Datamodel: Primary key as a combination of two relational models #3553 Closed nikolasburk added rfc/0-needs-spec and removed rfc/0-needs-spec labels Nov 20, 2018

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.

Data model (Reference), Learn about the concepts for building your data model with Prisma: In relational databases, this ID corresponds to a PRIMARY KEY constraint. or on multiple fields (also called composite or compound IDs) using @@id . The datamodel of your service configuration has two major roles: Define the underlying database schema (the models and fields are mapped to database tables). It is the foundation for the auto-generated CRUD operations of your Prisma client.

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:"loggedInUser@email.com_<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
}
        }

Multiple field unique constraint · Issue #171 · prisma/prisma1 · GitHub, And enforce the unique constraint on the combination like that? It's a lot Prisma Datamodel: Primary key as a combination of two relational models #3553. 1 Prisma Datamodel: Primary key as a combination of two relational models Jul 11 '19 0 How to find min and max time in Chat log conversation using pandas for calculating duration? Feb 14 '18

Primary key, In the relational model of databases, a primary key is a specific choice of a minimal set of In the SQL Standard, primary keys may consist of one or multiple columns. many developers and in some cases even theoreticians have come to regard surrogate primary keys as an inalienable part of the relational data model. Specifying a index with unique constraint on two fields to express that the combination of author and title must be unique. The field author field is a relation field and therefore the id of an Author will be part of the index.

Unique key, In database relational modeling and implementation, a unique key is a set of attributes (columns) within a relational database table (also called a relation), such that: the table does not have two distinct rows or records with the same values for This ensures that uniqueness is maintained in both the primary table and any  5 Prisma Datamodel: Primary key as a combination of two relational models Nov 19 '18 4 Login to a website using python Jul 5 '15 4 Neural-net Regression predicts same value for all test samples Apr 6 '17

Data modeling in YCQL, To create your data model, identify the patterns used to access data and the types of The primary key for this table consists of two columns - author and book_title . for these two attributes, and the combination of these values must be unique. Unlike the traditional relational data model, the document data model is not  Primary Key: Order# Foreign Keys: Salespersonid, Customerid. Field for Aggregations: Sales Total. ok, cool..we are grouping now but…what does Primary and Foreign really mean? Primary Key = The column ID for the table you’re in. It’s an unique value that makes a distinction between each row for the specified table

Comments
  • 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!
  • 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?
  • You should add @unique to the UniqueCustomerReview: String!