Hey SQL fans, check out foreign key ON UPDATE CASCADE

by Derek Sivers

Related link: http://www.postgresql.org/docs/7.4/static/tutorial-fk.html



In PostgreSQL:

Foreign key update show-off test:

-- every genre (Rock, Jazz, etc) has a unique id#
CREATE TABLE genre (
id serial PRIMARY KEY UNIQUE,
name varchar(16));

-- every subgenre (Metal, Bebop, etc) - references
-- the unique id# of the parent-genre
-- meaning genre_id *MUST* match genre.id
CREATE TABLE subgenre (
name varchar(16),
genre_id int REFERENCES genre(id) ON UPDATE CASCADE);

-- the important thing is "ON UPDATE CASCADE"
-- which means if the referenced genre.id is updated
-- then the changes "cascade" down to the referencing table
-- (in this case, the subgenre)

-- THIS MEANS: if I update the id# of a genre,
-- subgenre.genre_id will *automatically* update!

-- Let's test it...

INSERT INTO genre(name) VALUES ('Rock');
INSERT INTO genre(name) VALUES ('Jazz');

INSERT INTO subgenre VALUES('Metal', '1');
INSERT INTO subgenre VALUES('Bebop', '2');

-- look at it...
SELECT * FROM subgenre, genre WHERE subgenre.genre_id=genre.id;

name | genre_id | id | name
-------+----------+----+------
Metal | 1 | 1 | Rock
Bebop | 2 | 2 | Jazz


-- now alter that id# -- for whatever reason:
UPDATE genre set id='9' WHERE name='Rock';

-- look at it again...
SELECT * FROM subgenre, genre WHERE subgenre.genre_id=genre.id;

name | genre_id | id | name
-------+----------+----+------
Bebop | 2 | 2 | Jazz
Metal | 9 | 9 | Rock


-- it updated the genre_id in subgenre!!


I was doing all this kind of stuff manually in PHP whenever database tables were updated, but sometimes with old code, you may add a database table, and forget to update some 3-year-old PHP code, and accidently end up with database tables with unmatched ID #'s.

I LOVE this foreign-key thing.

1 Comments

aristotle
2004-10-02 12:51:34
See?
That's what people are talking about when they belittle MySQL for being a toy, not a real database, a glorified flatfile system, etc pp. :-)