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 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. If you want to know more about using relationships with Prisma, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma 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. If you want to know more about the many-to-many relationships, read API with NestJS #33. Managing PostgreSQL relationships with Prisma We can also see that Prisma chose ON DELETE CASCADE ON UPDATE CASCADE for the foreign keys. The catch is tha