API with NestJS #135. Referential actions and foreign keys in PostgreSQL with Prisma

NestJS SQL

This entry is part 135 of 158 in the API with NestJS

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

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

Restrict

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

users.service.ts

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 enum.

prisma-enum.ts

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

Cascade

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

Therefore,  means that if we change the id of an existing user, the corresponding is updated automatically.

On the other hand, 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

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

SetNull

We can see that the migration Prisma generated contains . It means that if we delete an address that belongs to a user, PostgreSQL automatically sets 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 attribute arguments. For example, let’s adjust the relationship between the User and the Article models.

schema.prisma

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

Writing caused Prisma to recreate the foreign key constraint, this time with . 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

NoAction

The NoAction referential action is similar to Restrict.

schema.prisma

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

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

migration.sql

The crucial part of the above migration is that it creates the 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 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 table, we need to create it explicitly.

schema.prisma

Since we are using the 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.

Series Navigation<< API with NestJS #134. Aggregating statistics with PostgreSQL and PrismaAPI with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range types >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments