pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFOREIGN KEYs ... I think ...
7+ messages / 5 participants
[nested] [flat]
* FOREIGN KEYs ... I think ...
@ 2006-01-05 01:49 Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 01:54 ` Re: FOREIGN KEYs ... I think ... Tom Lane <tgl@sss.pgh.pa.us>
2006-01-05 03:58 ` Re: FOREIGN KEYs ... I think ... nospam@hardgeus.com
2006-01-06 01:23 ` Re: FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 3 replies; 7+ messages in thread
From: Marc G. Fournier @ 2006-01-05 01:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FOREIGN KEYs ... I think ...
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
@ 2006-01-05 01:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-05 03:12 ` Re: FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-01-05 01:54 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FOREIGN KEYs ... I think ...
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 01:54 ` Re: FOREIGN KEYs ... I think ... Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-05 03:12 ` Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 03:31 ` Re: FOREIGN KEYs ... I think ... Jim Johannsen <jjsa@gvtc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Marc G. Fournier @ 2006-01-05 03:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FOREIGN KEYs ... I think ...
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 01:54 ` Re: FOREIGN KEYs ... I think ... Tom Lane <tgl@sss.pgh.pa.us>
2006-01-05 03:12 ` Re: FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
@ 2006-01-05 03:31 ` Jim Johannsen <jjsa@gvtc.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jim Johannsen @ 2006-01-05 03:31 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FOREIGN KEYs ... I think ...
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
@ 2006-01-05 03:58 ` nospam@hardgeus.com
2006-01-05 11:39 ` Re: FOREIGN KEYs ... I think ... Leif B. Kristensen <leif@solumslekt.org>
2 siblings, 1 reply; 7+ messages in thread
From: nospam@hardgeus.com @ 2006-01-05 03:58 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FOREIGN KEYs ... I think ...
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 03:58 ` Re: FOREIGN KEYs ... I think ... nospam@hardgeus.com
@ 2006-01-05 11:39 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2006-01-05 11:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FOREIGN KEYs ... I think ...
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
@ 2006-01-06 01:23 ` Marc G. Fournier <scrappy@postgresql.org>
2 siblings, 0 replies; 7+ messages in thread
From: Marc G. Fournier @ 2006-01-06 01:23 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-01-06 01:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-05 01:49 FOREIGN KEYs ... I think ... Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 01:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-05 03:12 ` Marc G. Fournier <scrappy@postgresql.org>
2006-01-05 03:31 ` Jim Johannsen <jjsa@gvtc.com>
2006-01-05 03:58 ` nospam@hardgeus.com
2006-01-05 11:39 ` Leif B. Kristensen <leif@solumslekt.org>
2006-01-06 01:23 ` Marc G. Fournier <scrappy@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox