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”