Back to Tutorials
8 min read

Referential actions and foreign keys in PostgreSQL with Prisma

A foreign key is a column that connects two tables. A constraint keeps this connection in check, and PostgreSQL ensures the foreign keys point to the correct…

November 27, 2023

A foreign key is a column that connects two tables. A constraint keeps this connection in check, and PostgreSQL ensures the foreign keys point to the correct row. Therefore, we need to think about what happens when we delete a record that’s connected this way. In this article, we learn more about foreign keys and handle them with referential actions using Prisma.

So far, in this series, we’ve created the following schema:

schema.prisma#
model Category {
  id       Int       @id @default(autoincrement())
  name     String
  articles Article[]
}
 
model Article {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  upvotes    Int        @default(0)
  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
  authorId   Int
  categories Category[]
}
 
model Address {
  id      Int    @id @default(autoincrement())
  street  String
  city    String
  country String
  user    User?
}
 
model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  password  String
  articles  Article[]
  address   Address?  @relation(fields: [addressId], references: [id])
  addressId Int?      @unique
}

Mandatory relationships and their default behavior#

In our schema, we can see a relationship between the User and Article models. Every article needs to have an author, which creates a one-to-many relationship.

Let’s inspect the migration Prisma generated for us.

migration.sql#
-- AlterTable
ALTER TABLE "Article" ADD COLUMN     "authorId" INTEGER NOT NULL;
 
-- AddForeignKey
ALTER TABLE "Article" ADD CONSTRAINT "Article_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Restrict#

Above, we see ON DELETE RESTRICT. This means that PostgreSQL prevents us from deleting users if they have any articles. Fortunately, we can handle this error with the try...catch block.

users.service.ts#
import { ConflictException, Injectable, NotFoundException } from "@nestjs/common"
import { PrismaError } from "../database/prisma-error.enum"
import { Prisma } from "@prisma/client"
import { PrismaService } from "../database/prisma.service"
@Injectable()
export class UsersService {
  constructor(private readonly prismaService: PrismaService) {}
  async delete(userId: number) {
    try {
      return await this.prismaService.user.delete({
        where: {
          id: userId,
        },
      })
    } catch (error) {
      if (!(error instanceof Prisma.PrismaClientKnownRequestError)) {
        throw error
      }
      if (error.code === PrismaError.RecordDoesNotExist) {
        throw new NotFoundException()
      }
      const affectedField = error.meta?.field_name
      if (
        error.code === PrismaError.ForeignKeyConstraintViolated &&
        typeof affectedField === "string" &&
        affectedField.toLowerCase().includes("article")
      ) {
        throw new ConflictException("Can't remove the user that is an author of some articles")
      }
      throw error
    }
  } // ...
}

If Prisma fails to delete the User because they have some articles, it throws an error. We can then catch it and respond with the 409 Conflict response code. To achieve the above, we need to adjust our PrismaError enum.

prisma-enum.ts#
export enum PrismaError {
  RecordDoesNotExist = 'P2025',
  UniqueConstraintViolated = 'P2002',
  ForeignKeyConstraintViolated = 'P2003',
}

Choosing ON UPDATE RESTRICT would cause PostgreSQL to prevent us from changing the ID of a user with some articles.

Cascade#

In our migration, we can see ON UPDATE CASCADE. In PostgreSQL, “cascade” means that the database will try to adjust to changes we make automatically.

Therefore,  FOREIGN KEY ("authorId") REFERENCES "User"("id") ON UPDATE CASCADE means that if we change the id of an existing user, the corresponding authorId is updated automatically.

On the other hand, ON DELETE CASCADE would mean that deleting a particular user would cause PostgreSQL to delete all related articles.

Optional relationships and their default behavior#

In our Prisma schema, we have a relationship between the User and Address models. What’s crucial is that the address is optional.

migration.sql#
-- AlterTable
ALTER TABLE "User" ADD COLUMN     "addressId" INTEGER;
 
-- CreateTable
CREATE TABLE "Address" (
    "id" SERIAL NOT NULL,
    "street" TEXT NOT NULL,
    "city" TEXT NOT NULL,
    "country" TEXT NOT NULL,
 
    CONSTRAINT "Address_pkey" PRIMARY KEY ("id")
);
 
-- CreateIndex
CREATE UNIQUE INDEX "User_addressId_key" ON "User"("addressId");
 
-- AddForeignKey
ALTER TABLE "User" ADD CONSTRAINT "User_addressId_fkey" FOREIGN KEY ("addressId") REFERENCES "Address"("id") ON DELETE SET NULL ON UPDATE CASCADE;

Similar to mandatory relationships, we can see ON UPDATE CASCADE. It means that if we change the ID of a particular existing address, the addressId is adjusted automatically.

SetNull#

We can see that the migration Prisma generated contains FOREIGN KEY ("addressId") REFERENCES "Address"("id") ON DELETE SET NULL. It means that if we delete an address that belongs to a user, PostgreSQL automatically sets addressId to null.

It’s important to acknowledge that this behavior only makes sense with optional relationships. Using it with required relationships would lead to errors since the foreign key would not be nullable.

Changing the referential action#

Prisma allows us to change the default referential actions through additional @relation() attribute arguments. For example, let’s adjust the relationship between the User and the Article models.

schema.prisma#
model Article {
  id       Int     @id @default(autoincrement())
  title    String
  content  String?
  upvotes  Int     @default(0)
  author   User    @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId Int
}

Above, we specified that we want to change the delete behavior to cascade. Let’s generate the migration and see the SQL code.

schema.prisma#
-- DropForeignKey
ALTER TABLE "Article" DROP CONSTRAINT "Article_authorId_fkey";
 
-- AddForeignKey
ALTER TABLE "Article" ADD CONSTRAINT "Article_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Writing onDelete: Cascade caused Prisma to recreate the foreign key constraint, this time with ON DELETE CASCADE. This means that if we delete a user, PostgreSQL also deletes their articles automatically.

SetDefault#

When we choose the SetDefault referential action, we tell Prisma to set the foreign key to its default value when the entity is updated, or deleted.

schema.prisma#
model Book {
  id         Int     @id @default(autoincrement())
  title      String
  authorName String? @default("anonymous")
  author     Writer? @relation(fields: [authorName], references: [name], onDelete: SetDefault, onUpdate: SetDefault)
}
 
model Writer {
  name String @id
  books Book[]
}

NoAction#

The NoAction referential action is similar to Restrict.

schema.prisma#
model Article {
  id       Int     @id @default(autoincrement())
  title    String
  content  String?
  upvotes  Int     @default(0)
  author   User    @relation(fields: [authorId], references: [id], onDelete: Restrict)
  authorId Int
}

There is a subtle difference between them, however. With NoAction, when we try to delete or update a row in the parent table, PostgreSQL checks at the end of the transaction if any foreign key references in the child table would be violated. If there are, the transaction is rolled back. However, it allows you to temporarily have a state within a transaction where the foreign key relationship is violated, as long as it’s resolved by the end of the transaction. When we use Restrict, on the other hand, PostgreSQL checks for foreign key violations immediately and doesn’t allow the transaction to proceed if violations exist, not even temporarily within a transaction.

Many-to-many relationships#

In our schema, we have a many-to-many relationship between the Article and Category models. An article can belong to many categories, and a category can contain multiple articles.

schema.prisma#
model Category {
  id       Int       @id @default(autoincrement())
  name     String
  articles Article[]
}
 
model Article {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  upvotes    Int        @default(0)
  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
  authorId   Int
  categories Category[]
}

Let’s take a look at the migration that Prisma generates for the above schema.

migration.sql#
-- CreateTable
CREATE TABLE "Category" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
 
    CONSTRAINT "Category_pkey" PRIMARY KEY ("id")
);
 
-- CreateTable
CREATE TABLE "_ArticleToCategory" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL
);
 
-- ...
 
-- AddForeignKey
ALTER TABLE "_ArticleToCategory" ADD CONSTRAINT "_ArticleToCategory_A_fkey" FOREIGN KEY ("A") REFERENCES "Article"("id") ON DELETE CASCADE ON UPDATE CASCADE;
 
-- AddForeignKey
ALTER TABLE "_ArticleToCategory" ADD CONSTRAINT "_ArticleToCategory_B_fkey" FOREIGN KEY ("B") REFERENCES "Category"("id") ON DELETE CASCADE ON UPDATE CASCADE;

The crucial part of the above migration is that it creates the _ArticleToCategory table where the foreign key “A” references the article, and the foreign key “B” references the category.

We can also see that Prisma chose ON DELETE CASCADE ON UPDATE CASCADE for the foreign keys. The catch is that we can’t adjust them when using the above approach. If we want to have more control over the _ArticleToCategory table, we need to create it explicitly.

schema.prisma#
model ArticleCategory {
  article       Article  @relation(fields: [articleId], references: [id])
  articleId     Int
  category      Category @relation(fields: [categoryId], references: [id])
  categoryId    Int
 
  @@id([articleId, categoryId])
}
 
model Category {
  id                 Int                  @id @default(autoincrement())
  name               String
  articlesCategory ArticleCategory[]
}
 
model Article {
  id                 Int                  @id @default(autoincrement())
  title              String
  text               String?
  author             User                 @relation(fields: [authorId], references: [id])
  authorId           Int
  articlesCategories ArticleCategory[]
}

Since we are using the @relation() attribute explicitly in the above schema, we are free to adjust the referential actions.

Summary#

In this article, we explored the use of foreign keys in PostgreSQL and Prisma. Through the referential actions, we learned to control PostgreSQL’s behavior to implement cascading deletes or prevent deletions if dependencies exist. This knowledge can come in handy for managing database integrity and handling relationships between tables more effectively.

Referential actions and foreign keys in PostgreSQL with Prisma | NestJS.io