You can achieve a single-level CASCADE DELETE in PostgreSQL by defining foreign key constraints with the ON DELETE CASCADE option. This instructs PostgreSQL to automatically delete any rows in the child table that reference the deleted row in the parent table.
Here’s how you can do it:
Define Foreign Key Constraints: When creating or altering tables, specify the ON DELETE CASCADE option in the foreign key constraint definition. For example:
SQL
CREATE TABLE parent_table (
id SERIAL PRIMARY KEY
);
CREATE TABLE child_table (
id SERIAL PRIMARY KEY,
parent_id INTEGER REFERENCES parent_table(id) ON DELETE CASCADE
);
Perform a DELETE Operation: When you delete a row from the parent_table, PostgreSQL will automatically delete the corresponding rows in the child_table that have the same parent_id.
Important Notes:
Single-Level Cascade: By default, ON DELETE CASCADE cascades the deletion to all child tables, potentially leading to unintended consequences if you have a complex relationship structure.
Controlling Cascade Depth: To limit the cascade to a single level, you can manually delete rows in subsequent child tables after the initial cascade.
Careful Consideration: Use CASCADE DELETE with caution, as it can have irreversible effects on your data. Always test thoroughly before implementing it in production.
By following these steps and exercising caution, you can effectively implement single-level CASCADE DELETE in PostgreSQL to maintain data integrity and consistency.