Prisma One-to-Many Relation to SQL FOREIGN KEY

One-to-Many Relation Conversion

A one-to-many relationship in Prisma is defined using a combination of a scalar foreign key field, a @relation attribute, and a reverse relation field (array) on the related model.

Example Prisma Schema

model Author {
  id    Int    @id @default(autoincrement())
  name  String
  books Book[]

  @@map("authors")
}

model Book {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int    @map("author_id")
  author   Author @relation(fields: [authorId], references: [id])

  @@map("books")
}

Generated SQL

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

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors (id)
);

How the Conversion Works

1. Relation field (author Author) is skipped — it has no SQL column equivalent. It only exists in Prisma for type-safe queries.
2. Array field (books Book[]) is also skipped — the "many" side is represented by the foreign key on the child table.
3. Scalar field (authorId Int) becomes a regular SQL column.
4. @relation(fields: [authorId], references: [id]) generates a FOREIGN KEY constraint pointing from author_id to authors.id.

Optional Relations

If the relation is optional (the foreign key field is nullable), the FOREIGN KEY still exists but the column allows NULL:

model Book {
  id       Int     @id @default(autoincrement())
  title    String
  authorId Int?    @map("author_id")
  author   Author? @relation(fields: [authorId], references: [id])
}

This generates author_id INTEGER (without NOT NULL) plus the same FOREIGN KEY constraint.

Index on Foreign Key

For performance, it's common to add an index on the foreign key column. Use @@index([authorId]) in Prisma to generate a CREATE INDEX statement.