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:
1model Category {
2 id Int @id @default(autoincrement())
3 name String
4 articles Article[]
5}
6
7model Article {
8 id Int @id @default(autoincrement())
9 title String
10 content String?
11 upvotes Int @default(0)
12 author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
13 authorId Int
14 categories Category[]
15}
16
17model Address {
18 id Int @id @default(autoincrement())
19 street String
20 city String
21 country String
22 user User?
23}
24
25model User {
26 id Int @id @default(autoincrement())
27 email String @unique
28 name String
29 password String
30 articles Article[]
31 address Address? @relation(fields: [addressId], references: [id])
32 addressId Int? @unique
33}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.
1-- AlterTable
2ALTER TABLE "Article" ADD COLUMN "authorId" INTEGER NOT NULL;
3
4-- AddForeignKey
5ALTER 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.
1import {
2 ConflictException,
3 Injectable,
4 NotFoundException,
5} from '@nestjs/common';
6import { PrismaError } from '../database/prisma-error.enum';
7import { Prisma } from '@prisma/client';
8import { PrismaService } from '../database/prisma.service';
9
10@Injectable()
11export class UsersService {
12 constructor(private readonly prismaService: PrismaService) {}
13
14 async delete(userId: number) {
15 try {
16 return await this.prismaService.user.delete({
17 where: {
18 id: userId,
19 },
20 });
21 } catch (error) {
22 if (!(error instanceof Prisma.PrismaClientKnownRequestError)) {
23 throw error;
24 }
25 if (error.code === PrismaError.RecordDoesNotExist) {
26 throw new NotFoundException();
27 }
28 const affectedField = error.meta?.field_name;
29 if (
30 error.code === PrismaError.ForeignKeyConstraintViolated &&
31 typeof affectedField === 'string' &&
32 affectedField.toLowerCase().includes('article')
33 ) {
34 throw new ConflictException(
35 "Can't remove the user that is an author of some articles",
36 );
37 }
38 throw error;
39 }
40 }
41
42 // ...
43}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.
1export enum PrismaError {
2 RecordDoesNotExist = 'P2025',
3 UniqueConstraintViolated = 'P2002',
4 ForeignKeyConstraintViolated = 'P2003',
5}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.
1-- AlterTable
2ALTER TABLE "User" ADD COLUMN "addressId" INTEGER;
3
4-- CreateTable
5CREATE TABLE "Address" (
6 "id" SERIAL NOT NULL,
7 "street" TEXT NOT NULL,
8 "city" TEXT NOT NULL,
9 "country" TEXT NOT NULL,
10
11 CONSTRAINT "Address_pkey" PRIMARY KEY ("id")
12);
13
14-- CreateIndex
15CREATE UNIQUE INDEX "User_addressId_key" ON "User"("addressId");
16
17-- AddForeignKey
18ALTER 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.
1model Article {
2 id Int @id @default(autoincrement())
3 title String
4 content String?
5 upvotes Int @default(0)
6 author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
7 authorId Int
8}Above, we specified that we want to change the delete behavior to cascade. Let’s generate the migration and see the SQL code.
1-- DropForeignKey
2ALTER TABLE "Article" DROP CONSTRAINT "Article_authorId_fkey";
3
4-- AddForeignKey
5ALTER 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.
1model Book {
2 id Int @id @default(autoincrement())
3 title String
4 authorName String? @default("anonymous")
5 author Writer? @relation(fields: [authorName], references: [name], onDelete: SetDefault, onUpdate: SetDefault)
6}
7
8model Writer {
9 name String @id
10 books Book[]
11}NoAction
The NoAction referential action is similar to Restrict.
1model Article {
2 id Int @id @default(autoincrement())
3 title String
4 content String?
5 upvotes Int @default(0)
6 author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
7 authorId Int
8}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.
1model Category {
2 id Int @id @default(autoincrement())
3 name String
4 articles Article[]
5}
6
7model Article {
8 id Int @id @default(autoincrement())
9 title String
10 content String?
11 upvotes Int @default(0)
12 author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
13 authorId Int
14 categories Category[]
15}Let’s take a look at the migration that Prisma generates for the above schema.
1-- CreateTable
2CREATE TABLE "Category" (
3 "id" SERIAL NOT NULL,
4 "name" TEXT NOT NULL,
5
6 CONSTRAINT "Category_pkey" PRIMARY KEY ("id")
7);
8
9-- CreateTable
10CREATE TABLE "_ArticleToCategory" (
11 "A" INTEGER NOT NULL,
12 "B" INTEGER NOT NULL
13);
14
15-- ...
16
17-- AddForeignKey
18ALTER TABLE "_ArticleToCategory" ADD CONSTRAINT "_ArticleToCategory_A_fkey" FOREIGN KEY ("A") REFERENCES "Article"("id") ON DELETE CASCADE ON UPDATE CASCADE;
19
20-- AddForeignKey
21ALTER 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 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.
1model ArticleCategory {
2 article Article @relation(fields: [articleId], references: [id])
3 articleId Int
4 category Category @relation(fields: [categoryId], references: [id])
5 categoryId Int
6
7 @@id([articleId, categoryId])
8}
9
10model Category {
11 id Int @id @default(autoincrement())
12 name String
13 articlesCategory ArticleCategory[]
14}
15
16model Article {
17 id Int @id @default(autoincrement())
18 title String
19 text String?
20 author User @relation(fields: [authorId], references: [id])
21 authorId Int
22 articlesCategories ArticleCategory[]
23}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.