PostgreSQL Cascading: Updates and Deletes

So something nice about a real database is cascading values, most commonly used to deletion, but you can use them for updates as well.  Let me give you a scenario: I track groups of data from my clients by a sub_id field.  As they add groups, their ids aren’t in a range.  If I wanted to consolidate them, I could update the sub_id (somewhere that won’t be stomped on by the sequence), but what about historical data.  Baring a cascade rule, PostgresSQL will stop the update.  A non-relational database like MySQL will just leave orphan foreign keys.  If you set Cascading, they come along for the ride.

View the following example:

CREATE TABLE A (a_id SERIAL PRIMARY KEY, letter char);
CREATE TABLE B (b_id SERIAL PRIMARY KEY,
a_id integer REFERENCES A(a_id) ON DELETE CASCADE ON UPDATE CASCADE,
letter char);

INSERT INTO A (letter) VALUES ('a'), ('b'), ('c'), ('d'), ('e');

INSERT INTO B(a_id, letter) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e');

SELECT * FROM A;
a_id | letter
------+--------
1 | a
2 | b
3 | c
4 | d
5 | e

SELECT * FROM B;
b_id | a_id | letter
------+------+--------
1 | 1 | a
2 | 2 | b
3 | 3 | c
4 | 4 | d
5 | 5 | e

DELETE FROM A WHERE a_id > 3;
-- We delete a few fields from the bottom table, and PostgreSQL magically cascades it to B

SELECT * FROM A;
a_id | letter
------+--------
1 | a
2 | b
3 | c

SELECT * FROM B;
b_id | a_id | letter
------+------+--------
1 | 1 | a
2 | 2 | b
3 | 3 | c

UPDATE A set a_id = a_id + 3;
-- This is the example above, renumbering the code

SELECT * FROM A;
a_id | letter
------+--------
4 | a
5 | b
6 | c

SELECT * FROM B;
b_id | a_id | letter
------+------+--------
1 | 4 | a
2 | 5 | b
3 | 6 | c

So we were able to renumber A, pass the values to B, without any updates to B. Pretty cool, huh?

One thought on “PostgreSQL Cascading: Updates and Deletes

Leave a Reply