Thread: FOREIGN KEYs ... I think ...
I'm not sure if I'm looking at (for) the right thing or not, but if I am, then I'm not finding any useful examples :( I have two tables, simplified as: CREATE TABLE incident_summary ( id serial, subject text, status boolean ); CREATE TABLE incident_comments ( id serial, incident_id int4, body text, comment_date timestamp, status boolean ); Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... It *looks* like it should be simple enough, I want incident_comments.status to change to incident_summary.status whenever incident_summary.status changes ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that I'm looking at the wrong thing ... So, what should I be searching on / reading for this one? Pointers preferred, especially one with some good examples :) Thanks ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Now, what I want to do is add a FOREIGN KEY (again, I think) that when
> incident_summary.status is changed (either closed, or reopened), the
> associated records in incident_comments are changed to the same state ...
Why not just get rid of the status column in incident_comments, and
treat incident_summary.status as the sole copy of the state? When you
need to get to it from incident_comments, you do a join.
The foreign key you really ought to have here is from
incident_comments.incident_id to incident_summary.id
(assuming that I've understood your schema correctly).
regards, tom lane
On Wed, 4 Jan 2006, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> Now, what I want to do is add a FOREIGN KEY (again, I think) that when >> incident_summary.status is changed (either closed, or reopened), the >> associated records in incident_comments are changed to the same state ... > > Why not just get rid of the status column in incident_comments, and > treat incident_summary.status as the sole copy of the state? When you > need to get to it from incident_comments, you do a join. I may end up getting to that point ... > The foreign key you really ought to have here is from > incident_comments.incident_id to incident_summary.id > (assuming that I've understood your schema correctly). 'k, where I'm getting lost here is how do I get status changed in _comments on UPDATE of incident_summary.id? There doesn't seem to be anything for ON UPDATE to 'run SQL query' or some such ... or I'm reading old docs :( This is the part that I'm having a bugger of a time wrapping my head around ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc Is the "assumption" that anytime there are comments the status changes? If I'm reading between the lines correctly, there could be a large number of comments before the status changes. So no need to change status until explicitly needed. If there is a specific "comment" that means a status change, you could code a trigger to check for the comment and then update the "status". I don't really like this solution but it could work. The front end/user should explicitly change the "status". Marc G. Fournier wrote: > On Wed, 4 Jan 2006, Tom Lane wrote: > >> "Marc G. Fournier" <scrappy@postgresql.org> writes: >> >>> Now, what I want to do is add a FOREIGN KEY (again, I think) that when >>> incident_summary.status is changed (either closed, or reopened), the >>> associated records in incident_comments are changed to the same >>> state ... >> >> >> Why not just get rid of the status column in incident_comments, and >> treat incident_summary.status as the sole copy of the state? When you >> need to get to it from incident_comments, you do a join. > > > I may end up getting to that point ... > >> The foreign key you really ought to have here is from >> incident_comments.incident_id to incident_summary.id >> (assuming that I've understood your schema correctly). > > > 'k, where I'm getting lost here is how do I get status changed in > _comments on UPDATE of incident_summary.id? There doesn't seem to be > anything for ON UPDATE to 'run SQL query' or some such ... or I'm > reading old docs :( This is the part that I'm having a bugger of a > time wrapping my head around ... > ---- > Marc G. Fournier Hub.Org Networking Services > (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: > 7615664 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
> Now, what I want to do is add a FOREIGN KEY (again, I think) that when > incident_summary.status is changed (either closed, or reopened), the > associated records in incident_comments are changed to the same state ... As the other responders mentioned, from the schema you described, it doesn't look like you really need two separate tables. However, if you do, foreign keys aren't what you're looking for to solve your problem. That's not what foreign keys do. The only thing a foreign key provides is a guarantee that if any records in B (the referencing table) still reference a record in table A (the referenced table) then you cannot delete that referenced record. As far as the actual data in table B being modified to match table A, that's something completely unrelated to foreign key integrity. That is something I would recommend maintaining at the application level. Or, if you're a trigger guy, do it in a trigger. John
On Thursday 05 January 2006 04:58, nospam@hardgeus.com wrote: >That's not what foreign keys do. The only thing a foreign key > provides is a guarantee that if any records in B (the referencing > table) still reference a record in table A (the referenced table) > then you cannot delete that referenced record. Just a little nitpick: A foreign key will also guarantee that you can't insert or update records with an fk in the referencing table that doesn't match one already entered into the referenced table. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE
Thanks to everyone for the responses ... ended up doing a trigger on the comments table that updates another table to maintain a "pointer" to the active record ... sped up the query that was hampering us from ~26 000ms to 47ms ... the killer part of the query was that each time it was havin gto figure out the 'active remark record' doing a 'max(create_time)' ... On Wed, 4 Jan 2006, Marc G. Fournier wrote: > > I'm not sure if I'm looking at (for) the right thing or not, but if I am, > then I'm not finding any useful examples :( > > I have two tables, simplified as: > > CREATE TABLE incident_summary ( > id serial, > subject text, > status boolean > ); > > CREATE TABLE incident_comments ( > id serial, > incident_id int4, > body text, > comment_date timestamp, > status boolean > ); > > Now, what I want to do is add a FOREIGN KEY (again, I think) that when > incident_summary.status is changed (either closed, or reopened), the > associated records in incident_comments are changed to the same state ... > > It *looks* like it should be simple enough, I want incident_comments.status > to change to incident_summary.status whenever incident_summary.status changes > ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that > I'm looking at the wrong thing ... > > So, what should I be searching on / reading for this one? Pointers > preferred, especially one with some good examples :) > > Thanks ... > > > ---- > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664