One-to-one relations

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's one 1-1-relation between User and Profile:

Relational databases
MongoDB
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 // 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 1-1-relation expresses the following:

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

Multi-field relations in relational databases

In relational databases only, you can also define use multi-field IDs to define a 1-1 relation:

Relational databases only
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)
}

1-1 relations in the database

Relational databases

The following example demonstrates how to create a 1-1 relation in 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):

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 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": "60d58e130011041800d209e1" }, "name": "Bob" }

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

{
"_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:

model User {
id Int @id @default(autoincrement())
profile Profile? // No relation scalar - must be optional
}

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. In the following example, profile and profileId are mandatory. This means that you cannot create a User without connecting or creating a Profile:

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

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

model User {
id Int @id @default(autoincrement())
profile Profile? @relation(fields: [profileId], references: [id]) // references `id` of `Profile`
profileId Int? // 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 forein key in the underlying database:

Relational databases
MongoDB
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 // relation scalar field (used in the `@relation` attribute above)
}

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 forein key in the underlying database:

Relational databases
MongoDB
model User {
id Int @id @default(autoincrement())
profile Profile? @relation(fields: [profileId], references: [id])
profileId Int? // relation scalar field (used in the `@relation` attribute above)
}
model Profile {
id Int @id @default(autoincrement())
user User?
}
Edit this page on GitHub