mroswell (4) [Avatar] Offline
#1
I feel lucky to know that fk = foreign key, and lu probably means lookup. but it would be helpful if those, along with pk and fki prepended characters (and any others you might use in the book) were explained.

The original ALTER TABLE PostgresSQLdocs don't make very clear what FOREIGN KEY or REFERENCES means, though they do provide this example without explanation:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

Likewise your book offers a short blurb for the following on PDF pages 24 and 25 (book pages 21 and 22)

ALTER TABLE ch01.restaurants
ADD CONSTRAINT fk_restaurants_lu_franchises
FOREIGN KEY (franchise)
REFERENCES ch01.lu_franchises (id)
ON UPDATE CASCADE ON DELETE RESTRICT;

I wish that were spelled out in a bit more detail. I think I'm finally getting the sense of that last line: that it refers to two groups of things: (on update cascade) (on delete restrict). I found it a bit confusing when you wrote on PDF pg 23/book page 21:
Adding CASCADE UPDATE DELETE rules....

Maybe that line should say Adding UPDATE and DELETE rules when we add foreign key relationships will allow us to change the franchise id for our franchises if we want and have those update the restaurants table automatically. An example of an UPDATE rule is: ON UPDATE CASCADE. An example of a DELETE rule is ON DELETE RESTRICT.

(Am I saying that properly?)

In any case, I'd never have known how to write your ALTER command based on
http://www.postgresql.org/docs/9.2/static/sql-altertable.html
so I'm glad you're including it in the book! Just wish there were a bit more documentation to explain it.

One more thought: judging by the docs page, would it make more sense if the example said:
...
ADD CONSTRAINT fk_restaurants_lu_franchises FOREIGN KEY (franchise) REFERENCES ch01.lu_franchises (id)
...
rather than splitting that across three lines?
regina.leo (259) [Avatar] Offline
#2
Re: explain ALTER TABLE details
The appendix D and Appendix C in the book we plan to explain much of this. Those are all side topics from PostGIS so we pushed them to the appendix. It's pretty standard stuff in not just for PostgreSQL, but other relational databases.

It would make sense to have on one line, except the Manning frameork doesn't allow lines longer than 70 characters for printing reasons, so a lot of stuff we would have like to include as one line, we just broke out rather than having annoying line continuation characters.