Convert SQL One-to-One Foreign Keys to Prisma Relations

One-to-One Relations

A one-to-one relationship in SQL is a foreign key with a UNIQUE constraint, ensuring each parent has at most one related child. Prisma models this with singular relation fields on both sides.

Example SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE,
  bio TEXT,
  avatar_url VARCHAR(2048),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Generated Prisma Schema

model User {
  id      Int      @id @default(autoincrement())
  name    String
  profile Profile?

  @@map("users")
}

model Profile {
  id        Int     @id @default(autoincrement())
  userId    Int     @unique @map("user_id")
  bio       String? @db.Text
  avatarUrl String? @db.VarChar(2048) @map("avatar_url")
  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

How It Works

1. The foreign key column user_id has a UNIQUE constraint — this signals a 1:1 relationship.
2. In Prisma, the child model (Profile) holds the @relation with fields and references.
3. The parent model (User) has an optional back-reference Profile? (optional because the profile may not exist yet).
4. The @unique attribute on userId in Prisma enforces the one-to-one constraint.

Required vs Optional

// Optional: user may or may not have a profile
model User {
  profile Profile?
}

// Required: every user must have a profile
model User {
  profile Profile
}

Whether the relation is optional on the parent side depends on your business logic. The SQL schema alone cannot express this — a NOT NULL FK on the child side means "every profile must have a user" but does not mean "every user must have a profile."

Compared to Embedding

An alternative to a separate 1:1 table is to put all fields in the parent table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  bio TEXT,
  avatar_url VARCHAR(2048)
);

Use a separate table when:

• The related data is large and rarely accessed
• You want independent permission control
• The related data has its own lifecycle (e.g., separate created_at/updated_at)

The converter generates a 1:1 relation whenever it detects a UNIQUE foreign key, and adds the bidirectional relation fields automatically.

Self-Referential One-to-One

A less common pattern is self-referential 1:1:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  mentor_id INTEGER UNIQUE,
  FOREIGN KEY (mentor_id) REFERENCES employees(id)
);

model Employee {
  id       Int       @id @default(autoincrement())
  name     String
  mentorId Int?      @unique @map("mentor_id")
  mentor   Employee? @relation("EmployeeMentor", fields: [mentorId], references: [id])
  mentee   Employee? @relation("EmployeeMentor")

  @@map("employees")
}