Skip to main content

One-to-many relations

This page introduces one-to-many relations and explains how to use them in your Prisma schema.

Overview​

One-to-many (1-n) relations refer to relations where one record on one side of the relation can be connected to zero or more records on the other side. In the following example, there is one one-to-many relation between the User and Post models:

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int
}

Note The posts field does not "manifest" in the underlying database schema. On the other side of the relation, the annotated relation field author and its relation scalar authorId represent the side of the relation that stores the foreign key in the underlying database.

This one-to-many relation expresses the following:

  • "a user can have zero or more posts"
  • "a post must always have an author"

In the previous example, the author relation field of the Post model references the id field of the User model. You can also reference a different field. In this case, you need to mark the field with the @unique attribute, to guarantee that there is only a single User connected to each Post. In the following example, the author field references an email field in the User model, which is marked with the @unique attribute:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique // <-- add unique attribute
  posts Post[]
}

model Post {
  id          Int    @id @default(autoincrement())
  authorEmail String
  author      User   @relation(fields: [authorEmail], references: [email])
}
warning

In MySQL, you can create a foreign key with only an index on the referenced side, and not a unique constraint. In Prisma ORM versions 4.0.0 and later, if you introspect a relation of this type it will trigger a validation error. To fix this, you will need to add a @unique constraint to the referenced field.

Multi-field relations in relational databases​

In relational databases only, you can also define this relation using multi-field IDs/composite key:

model User {
  firstName String
  lastName  String
  post      Post[]

  @@id([firstName, lastName])
}

model Post {
  id              Int    @id @default(autoincrement())
  author          User   @relation(fields: [authorFirstName, authorLastName], references: [firstName, lastName])
  authorFirstName String // relation scalar field (used in the `@relation` attribute above)
  authorLastName  String // relation scalar field (used in the `@relation` attribute above)
}

1-n relations in the database​

Relational databases​

The following example demonstrates how to create a 1-n relation in SQL:

CREATE TABLE "User" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY,
    "authorId" integer NOT NULL,
    FOREIGN KEY ("authorId") REFERENCES "User"(id)
);

Since there's no UNIQUE constraint on the authorId column (the foreign key), you can create multiple Post records that point to the same User record. This makes the relation a one-to-many rather than a one-to-one.

The following example demonstrates how to create a 1-n relation in SQL using a composite key (firstName and lastName):

CREATE TABLE "User" (
    firstName TEXT,
    lastName TEXT,
    PRIMARY KEY ("firstName","lastName")
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY,
    "authorFirstName" TEXT NOT NULL,
    "authorLastName" TEXT NOT NULL,
    FOREIGN KEY ("authorFirstName", "authorLastName") REFERENCES "User"("firstName", "lastName")
);

Comparing one-to-one and one-to-many relations​

In relational databases, the main difference between a 1-1 and a 1-n-relation is that in a 1-1-relation the foreign key must have a UNIQUE constraint defined on it.

MongoDB​

For MongoDB, Prisma ORM currently uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases.

The following MongoDB document represents a User:

{ "_id": { "$oid": "60d5922d00581b8f0062e3a8" }, "name": "Ella" }

Each of the following Post MongoDB documents has an authorId field which references the same user:

[
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3a9" },
    "title": "How to make sushi",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  },
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3aa" },
    "title": "How to re-install Windows",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  }
]

Comparing one-to-one and one-to-many relations​

In MongoDB, the only difference between a 1-1 and a 1-n is the number of documents referencing another document in the database - there are no constraints.

Required and optional relation fields in one-to-many relations​

A 1-n-relation always has two relation fields:

The annotated relation field and relation scalar of a 1-n relation can either both be optional, or both be mandatory. On the other side of the relation, the list is always mandatory.

Optional one-to-many relation​

In the following example, you can create a Post without assigning a User:

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int   @id @default(autoincrement())
  author   User? @relation(fields: [authorId], references: [id])
  authorId Int?
}

Mandatory one-to-many relation​

In the following example, you must assign a User when you create a Post:

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int
}