ContextQMD
Back to Prisma

One-to-one relations

apps/docs/content/docs.v6/orm/prisma-schema/data-model/relations/one-to-one-relations.mdx

latest9.3 KB
Original Source

:::info[Quick summary] Learn how to define and use one-to-one (1-1) relations in your Prisma schema, including modeling choices and key concepts. :::

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

<details> <summary>Questions answered in this page</summary>
  • How do I model one-to-one relations?
  • Which side should store the foreign key?
  • How do optional vs required 1-1 work?
</details>

Overview

One-to-one (1-1) relations refer to relations where at most one record can be connected on both sides of the relation. In the example below, there is a one-to-one relation between User and Profile:

prisma
model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int  @unique // relation scalar field (used in the `@relation` attribute above)
}
prisma
model User {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
  profile Profile?
}

model Profile {
  id     String @id @default(auto()) @map("_id") @db.ObjectId
  user   User   @relation(fields: [userId], references: [id])
  userId String @unique @db.ObjectId // relation scalar field (used in the `@relation` attribute above)
}

The userId relation scalar is a direct representation of the foreign key in the underlying database. This one-to-one relation expresses the following:

  • "a user can have zero profiles or one profile" (because the profile field is optional on User)
  • "a profile must always be connected to one user"

In the previous example, the user relation field of the Profile 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 Profile. In the following example, the user field references an email field in the User model, which is marked with the @unique attribute:

prisma
model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique // <-- add unique attribute
  profile Profile?
}

model Profile {
  id        Int    @id @default(autoincrement())
  user      User   @relation(fields: [userEmail], references: [email])
  userEmail String @unique // relation scalar field (used in the `@relation` attribute above)
}
prisma
model User {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
  email   String   @unique // <-- add unique attribute
  profile Profile?
}

model Profile {
  id        String @id @default(auto()) @map("_id") @db.ObjectId
  user      User   @relation(fields: [userEmail], references: [email])
  userEmail String @unique @db.ObjectId // relation scalar field (used in the `@relation` attribute above)
}

:::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 use multi-field IDs to define a 1-1 relation:

prisma
model User {
  firstName String
  lastName  String
  profile   Profile?

  @@id([firstName, lastName])
}

model Profile {
  id            Int    @id @default(autoincrement())
  user          User   @relation(fields: [userFirstName, userLastName], references: [firstName, lastName])
  userFirstName String // relation scalar field (used in the `@relation` attribute above)
  userLastName  String // relation scalar field (used in the `@relation` attribute above)

  @@unique([userFirstName, userLastName])
}

1-1 relations in the database

Relational databases

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

sql
CREATE TABLE "User" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Profile" (
    id SERIAL PRIMARY KEY,
    "userId" INTEGER NOT NULL UNIQUE,
    FOREIGN KEY ("userId") REFERENCES "User"(id)
);

Notice that there is a UNIQUE constraint on the foreign key userId. If this UNIQUE constraint was missing, the relation would be considered a 1-n relation.

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

sql
CREATE TABLE "User" (
    firstName TEXT,
    lastName TEXT,
    PRIMARY KEY ("firstName","lastName")
);
CREATE TABLE "Profile" (
    id SERIAL PRIMARY KEY,
    "userFirstName" TEXT NOT NULL,
    "userLastName" TEXT NOT NULL,
    UNIQUE ("userFirstName", "userLastName")
    FOREIGN KEY ("userFirstName", "userLastName") REFERENCES "User"("firstName", "lastName")
);

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:

json
{ "_id": { "$oid": "60d58e130011041800d209e1" }, "name": "Bob" }

The following MongoDB document represents a Profile - notice the userId field, which references the User document's $oid:

json
{
  "_id": { "$oid": "60d58e140011041800d209e2" },
  "bio": "I'm Bob, and I like drawing.",
  "userId": { "$oid": "60d58e130011041800d209e1" }
}

Required and optional 1-1 relation fields

In a one-to-one relation, the side of the relation without a relation scalar (the field representing the foreign key in the database) must be optional:

prisma
model User {
  id      Int      @id @default(autoincrement())
  profile Profile? // No relation scalar - must be optional // [!code highlight]
}

This restriction was introduced in 2.12.0.

However, you can choose if the side of the relation with a relation scalar should be optional or mandatory.

Mandatory 1-1 relation

In the following example, profile and profileId are mandatory. This means that you cannot create a User without connecting or creating a Profile:

prisma
model User {
  id        Int     @id @default(autoincrement())
  profile   Profile @relation(fields: [profileId], references: [id]) // references `id` of `Profile`
  profileId Int     @unique // relation scalar field (used in the `@relation` attribute above)
}

model Profile {
  id   Int   @id @default(autoincrement())
  user User?
}

Optional 1-1 relation

In the following example, profile and profileId are optional. This means that you can create a user without connecting or creating a Profile:

prisma
model User {
  id        Int      @id @default(autoincrement())
  profile   Profile? @relation(fields: [profileId], references: [id]) // references `id` of `Profile`
  profileId Int?     @unique // relation scalar field (used in the `@relation` attribute above)
}

model Profile {
  id   Int   @id @default(autoincrement())
  user User?
}

Choosing which side should store the foreign key in a 1-1 relation

In 1-1 relations, you can decide yourself which side of the relation you want to annotate with the @relation attribute (and therefore holds the foreign key).

In the following example, the relation field on the Profile model is annotated with the @relation attribute. userId is a direct representation of the foreign key in the underlying database:

prisma
model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int  @unique // relation scalar field (used in the `@relation` attribute above)
}
prisma
model User {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
  profile Profile?
}

model Profile {
  id     String @id @default(auto()) @map("_id") @db.ObjectId
  user   User   @relation(fields: [userId], references: [id])
  userId String @unique @db.ObjectId
}

You can also annotate the other side of the relation with the @relation attribute. The following example annotates the relation field on the User model. profileId is a direct representation of the foreign key in the underlying database:

prisma
model User {
  id        Int      @id @default(autoincrement())
  profile   Profile? @relation(fields: [profileId], references: [id])
  profileId Int?     @unique // relation scalar field (used in the `@relation` attribute above)
}

model Profile {
  id   Int   @id @default(autoincrement())
  user User?
}
prisma
model User {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  profile   Profile? @relation(fields: [profileId], references: [id])
  profileId String?  @unique @db.ObjectId // relation scalar field (used in the `@relation` attribute above)
}

model Profile {
  id   String @id @default(auto()) @map("_id") @db.ObjectId
  user User?
}