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
:
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 onUser
) - "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:
model User {firstName StringlastName Stringprofile 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 foreign key in the underlying database:
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 foreign key in the underlying database:
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?}