Database Relations Types

This document provides comprehensive details on the various types of database relations supported by Prisma. It includes one-to-one, one-to-many, and many-to-many relationships, along with examples of how to create, connect, and disconnect related records using the Prisma PHP.

Optional Relation

For optional relations, you can use the "?" symbol to indicate that the field is optional and can be null. This is useful when you want to create a relationship between two models where one model is optional.

model UserAccount {
        id          Int         @id @default(autoincrement())
        username    String      @unique
        password    String

        userProfileId Int?       @unique
        userProfile UserProfile? @relation(fields: [userProfileId], references: [id])
    }

    model UserProfile {
        id          Int         @id @default(autoincrement())
        email       String      @unique
        fullName    String

        userAccount UserAccount?
    }

Note: In the example above, the userProfileId field in the UserAccount model is optional because it is defined as Int?, which allows it to be nullable. Similarly, the userProfile field in the UserAccount model is also optional and can be null. This applies to both one-to-one and one-to-many relationships.

One-to-One Relationship

A one-to-one relationship is a type of relationship where a record in one table is related to only one record in another table. In Prisma, you can create a one-to-one relationship by defining a field in the model that references another model.

The relation between the UserAccount and UserProfile models is an example of a one-to-one relationship. In this relationship, the userProfileId field in the UserAccount model is required and unique, serving as a foreign key that references the id field in the UserProfile model.

model UserAccount {
        id          Int         @id @default(autoincrement())
        username    String      @unique
        password    String

        userProfileId Int       @unique
        userProfile UserProfile @relation(fields: [userProfileId], references: [id])
    }

    model UserProfile {
        id          Int         @id @default(autoincrement())
        email       String      @unique
        fullName    String

        userAccount UserAccount?
    }

In the example above, the UserAccount model has a one-to-one relationship with the UserProfile model. The userProfileId field in the UserAccount model references the id field in the UserProfile model.

One-to-Many Relationship

A one-to-many relationship is a type of relationship where a record in one table is related to multiple records in another table. In Prisma, you can create a one-to-many relationship by defining a field in the model that references another model.

model User {
        id          Int         @id @default(autoincrement())
        username    String      @unique

        posts       Post[]
    }

    model Post {
        id          Int         @id @default(autoincrement())
        title       String
        content     String
        
        userId      Int
        user        User        @relation(fields: [userId], references: [id])
    }

In the example above, the User model has a one-to-many relationship with the Post model. The userId field in the Post model references the id field in the User model.

Implicit Many-to-Many Relationship

A many-to-many relationship is a type of relationship where a record in one table is related to multiple records in another table, and vice versa. In Prisma, you can create a many-to-many relationship by defining a field in the model that references another model.

model Post {
    id         Int        @id @default(autoincrement())
    title      String

    categories Category[]
  }
  
  model Category {
    id    Int    @id @default(autoincrement())
    name  String

    posts Post[]
  }

In the example above, the User model has a many-to-many relationship with the Post model. The posts field in the User model references the Post model, and the users field in the Post model references the User model.

Explicit Many-to-Many Relationship

An explicit many-to-many relationship is a type of relationship where a record in one table is related to multiple records in another table, and vice versa. In Prisma, you can create an explicit many-to-many relationship by defining a separate model that references both models.

model User {
        id          Int         @id @default(autoincrement())
        username    String      @unique

        userPosts   UserPost[]
    }

    model Post {
        id          Int         @id @default(autoincrement())
        title       String
        content     String

        userPosts   UserPost[]
    }

    model UserPost {
        userId      Int
        user        User        @relation(fields: [userId], references: [id])
        postId      Int
        post        Post        @relation(fields: [postId], references: [id])
    }

In the example above, the User model and the Post model have an explicit many-to-many relationship through the UserPost model. The userId field in the UserPost model references the id field in the User model, and the postId field in the UserPost model references the id field in the Post model.