PostgreSQL: DELETE with CASCADE
The DELETE ... CASCADE operation in PostgreSQL is used to remove rows from a table and automatically delete dependent rows in other related tables. This feature is beneficial when dealing with relational databases with foreign key constraints, as it maintains referential integrity by cascading deletions through related tables.
This guide provides an in-depth explanation of the CASCADE option with syntax, examples, and detailed code walkthroughs.
Syntax:
The basic syntax of the DELETE statement with cascading effects relies on the table’s foreign key constraints defined with the ON DELETE CASCADE clause:
DELETE FROM table_name WHERE condition;
Key Point: The CASCADE effect is triggered by the foreign key constraint and does not require explicit inclusion in the DELETE statement.
Example 1: Creating Tables with CASCADE
Code:
-- Create a parent table
CREATE TABLE authors (
author_id SERIAL PRIMARY KEY, -- Unique identifier for authors
name VARCHAR(100) NOT NULL -- Author's name
);
-- Create a child table with a foreign key referencing authors
CREATE TABLE books (
book_id SERIAL PRIMARY KEY, -- Unique identifier for books
title VARCHAR(200) NOT NULL, -- Title of the book
author_id INT REFERENCES authors(author_id) ON DELETE CASCADE -- Foreign key with CASCADE
);
Explanation:
1. authors: The parent table containing author details.
2. books: The child table where each book is associated with an author. The ON DELETE CASCADE ensures that if an author is deleted, their books are also removed.
Example 2: Inserting Data
Code:
-- Insert data into the authors table
INSERT INTO authors (name)
VALUES
('J.K. Rowling'),
('George R.R. Martin');
-- Insert data into the books table
INSERT INTO books (title, author_id)
VALUES
('Harry Potter and the Philosopher''s Stone', 1),
('A Game of Thrones', 2),
('A Clash of Kings', 2);
Explanation
Example 3: Deleting Data with CASCADE
Code:
-- Delete an author
DELETE FROM authors
WHERE author_id = 2;
-- Query the books table to check remaining records
SELECT * FROM books;
Explanation
1. The DELETE statement removes the author with author_id = 2.
2. The ON DELETE CASCADE automatically deletes all books authored by them, maintaining data integrity.
Output of the Books Table
After executing the above deletion:
book_id title author_id 1 Harry Potter and the Philosopher's Stone 1Key Considerations
1. Defining CASCADE:
2. Use Cases:
3. Performance:
Common Errors
1. Missing CASCADE Clause: Attempting to delete a parent row without the CASCADE clause in the foreign key will result in an error:
ERROR: update or delete on table "authors" violates foreign key constraint
2. Unintended Deletions: Cascading deletions can lead to loss of important data if not implemented carefully.
Best Practices
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4