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.