Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode

[Busser, Jim]

On 2013-08-01, at 12:32 AM, Karsten Hilbert <address@hidden>  wrote:

> On Thu, Aug 01, 2013 at 01:04:55AM +0000, Jim Busser wrote:
> 
>> It appears that the procedure was not deleted, since I can still find it at 
>> the bottom of
>> 
>>      select * from clin.procedure ;
>> 
>> but the problem now is that the episode to which it is keyed
>> 
>>      fk_episode = 397
>> 
>> no longer exists:
>> 
>>      select pk from clin.episode WHERE pk = 397 ;
>>       pk
>>      ----
>>       (0 rows) 
> 
> I would suggest checking your database. It seems to be
> lacking a foreign key from clin.clin_root_item.fk_episode to
> clin.episode.pk.

What kind of 'check' are you suggesting?

When I do

        gnumed_v18=> select modified_when,
                pk_item, clin_when, fk_encounter, fk_episode
                FROM clin.clin_root_item
                WHERE fk_episode = 397

I get no rows, and

        gnumed_v18=> select * from clin.clin_root_item
        WHERE fk_episode IS NULL ;

yields no rows. The following, FYI, begins with the last row which preceded 
yesterday's offence:

        SELECT modified_when, pk_item, clin_when, fk_encounter, fk_episode FROM 
clin.clin_root_item

         …
         2013-07-26 11:22:41.05458-07  |    1056 | 2013-07-26 11:22:41.05458-07 
 |         1135 |        390
         2013-07-29 12:07:54.183744-07 |    1061 | 2013-07-29 
12:06:45.937352-07 |         1143 |        348
         2013-07-29 12:06:46.016094-07 |    1062 | 2013-07-29 
12:06:46.016094-07 |         1143 |        348
         2013-07-29 12:06:45.881746-07 |    1060 | 2013-07-29 
12:06:45.881746-07 |         1143 |        348
         2013-07-29 12:06:44.830139-07 |    1059 | 2013-07-29 
12:06:44.830139-07 |         1143 |        348
         2013-07-29 12:27:03.146207-07 |    1063 | 2013-07-29 
12:17:21.823773-07 |         1144 |        391
         2013-07-29 12:46:18.18756-07  |    1065 | 2013-07-29 12:46:18.18756-07 
 |         1144 |        392
         2013-07-29 12:44:07.6843-07   |    1064 | 2013-07-29 12:44:07.6843-07  
 |         1144 |        392
         2013-07-29 12:59:19.378548-07 |    1066 | 2013-07-29 
12:59:19.378548-07 |         1145 |        393
         2013-07-29 13:00:50.549529-07 |    1067 | 2013-07-29 
13:00:50.549529-07 |         1145 |        393
         2013-07-31 16:31:18.4447-07   |    1068 | 2013-05-30 00:00:00-07       
 |         1156 |        108
         2013-07-31 17:03:07.062646-07 |    1069 | 2012-05-23 00:00:00-07       
 |         1157 |        396
        (1030 rows)

also,

        gnumed_v18=> select * from clin.clin_root_item
        WHERE fk_episode IS NULL ;

yields no rows.

The orphaned procedure, which has a fk_episode of 377, has a pk_item of 1070 
however if I do

        select pk_item, narrative from clin.clin_root_item order by pk_item;

my clin.clin_root_item table only goes up to 1069. Is it possible that deleting 
the episode succeeded to delete a row in the root item table (despite this was 
not intended to be possible)?

Do you wish me to test this?

-- JIm