From: Jim Johannsen <jjsa@gvtc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: FOREIGN KEYs ... I think ...
Date: Wed, 04 Jan 2006 21:31:47 -0600
Message-ID: <43BC9323.9000402@gvtc.com> (raw)
In-Reply-To: <20060104230722.N1088@ganymede.hub.org>
References: <20060104213210.A1088@ganymede.hub.org>
<27177.1136426087@sss.pgh.pa.us>
<20060104230722.N1088@ganymede.hub.org>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jjsa@gvtc.com
Subject: Re: FOREIGN KEYs ... I think ...
In-Reply-To: <43BC9323.9000402@gvtc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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