Скачать или смотреть How to Implement Cascade DELETE in PostgreSQL for Related Tables

Discover how to effectively use `Cascade DELETE` in PostgreSQL to manage relational data, ensuring that deleting one entry also removes all associated records from related tables seamlessly.
---
This video is based on the question https://stackoverflow.com/q/62353139/ asked by the user 'ramos jc' ( https://stackoverflow.com/u/13731102/ ) and on the answer https://stackoverflow.com/a/62353193/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Cascade DELETE to row of another table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Implement Cascade DELETE in PostgreSQL for Related Tables

Managing relational databases can sometimes present challenges, especially when it comes to deleting entries that are interlinked between tables. For instance, when deleting a record from one table, you might want to automatically remove related entries from another table. This is where the Cascade DELETE feature comes into play. In this post, we'll explore how this feature works and how to effectively use it in PostgreSQL.

The Problem

Let's consider a scenario in a book database where we have two tables: livros (books) and capitulos (chapters). When you attempt to delete a book (a record from the livros table) that has corresponding chapters in the capitulos table, you want to ensure that all those related chapters are also deleted.

If you've tried to implement this using triggers, you might have encountered some hurdles, such as success messages on deletion but finding that no records were actually deleted.

Understanding Cascade DELETE

The simplest way to achieve the behavior of deleting related records is not by using triggers, but rather by defining a foreign key with the ON DELETE CASCADE option. Here’s how it works:

Foreign Key with ON DELETE CASCADE

Creating the tables:
You should define the capitulos table, ensuring that its foreign key references the primary key in the livros table with the cascading delete functionality.

[[See Video to Reveal this Text or Code Snippet]]

By doing this, whenever a record from livros is deleted, all related records in capitulos will automatically be removed as well.

How to Execute the DELETE Command

After you've successfully created the foreign key relationship, you can simply execute the delete command on the livros table. For example:

[[See Video to Reveal this Text or Code Snippet]]

If a book with id_livro 30 exists and it has associated chapters, both the book and its chapters will be removed from the database seamlessly.

Reworking Existing Triggers

If you already have a DELETE trigger in place for handling deletions, it's crucial to make adjustments to ensure it utilizes the appropriate references. Here’s how you could revise it:

Create or replace the function:
Instead of using the NEW pseudo-table, use OLD, which contains the values of the record being deleted.

[[See Video to Reveal this Text or Code Snippet]]

Create the trigger:
Create the trigger to call this function before deleting a book.

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Using the ON DELETE CASCADE option in PostgreSQL is a straightforward and efficient way to manage related data in your database. It simplifies your code by reducing the need for complex triggers, while ensuring that your data integrity is maintained. Whether you are starting from scratch or modifying existing triggers, implementing this feature will streamline the deletion process across related tables.

By following the guidelines and structures outlined above, you can effectively manage entries in your relational database while eliminating the overhead of manual deletions. Happy database management!