The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

mroswell (4) [Avatar] Offline
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)

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:

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
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 (265) [Avatar] Offline
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.