[OPEN-ILS-DEV] Removing records - Training Opportunity...

[Grant Johnson]

Hey...
do I have my relationships wrong?
I don't think so.....

 Table References:
 biblio.record_entry
 id = 14497

 metabib.metarecord
 id=17448     master_record=14497

 metaib.metarecord_source_map
 id=14710      metarecord=17448    source=14497

This Fails.
BEGIN;
 DELETE from metabib.metarecord_source_map  where source IN (SELECT id FROM biblio.record_entry where tcn_value like 'cis%');
 DELETE from metabib.metarecord where master_record IN (SELECT id  FROM biblio.record_entry where tcn_value like 'cis%');
 DELETE from biblio.record_entry where tcn_value like 'cis%';
CREATE RULE protect_bib_rec_delete AS ON DELETE TO biblio.record_entry DO INSTEAD  UPDATE  biblio.record_entry SET deleted = true;
COMMIT;

It fails on the second set of commands.
evergreen=#  DELETE from metabib.metarecord where master_record IN (SELECT id  FROM biblio.record_entry where tcn_value like 'cis%');
ERROR:  update or delete on "metarecord" violates foreign key constraint "metabib_metarecord_source_map_metarecord_fkey" on "metarecord_source_map"
DETAIL:  Key (id)=(41434) is still referenced from table "metarecord_source_map".


But this tells me that everything should be fine.
evergreen=# \d metabib.metarecord_source_map
                              Table "metabib.metarecord_source_map"
   Column   |  Type  |                                 Modifiers
------------+--------+----------------------------------------------------------------------------
 id         | bigint | not null default nextval('metabib.metarecord_source_map_id_seq'::regclass)
 metarecord | bigint | not null
 source     | bigint | not null
Indexes:
    "metarecord_source_map_pkey" PRIMARY KEY, btree (id)
    "metabib_metarecord_source_map_metarecord_idx" btree (metarecord)
    "metabib_metarecord_source_map_source_record_idx" btree (source)
Foreign-key constraints:
    "metabib_metarecord_source_map_metarecord_fkey" FOREIGN KEY (metarecord) REFERENCES metabib.metarecord(id) DEFERRABLE INITIALLY DEFERRED
    "metabib_metarecord_source_map_source_fkey" FOREIGN KEY (source) REFERENCES biblio.record_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

-- 

F. Grant Johnson
  Systems Coordinator
  Robertson Library
  University of Prince Edward Island

>>> On 2008/10/22 at 6:10 PM, in message
<da83a5eb0810221410w13b1add8v14925f24fcb6ff6a at mail.gmail.com>, "James Fournie"
<jfournie at sitka.bclibraries.ca> wrote:
> Hi Grant,
> 
> Looks like you want to use IN instead of =
> 
> # DELETE from metabib.metarecord_source_map where source IN (SELECT id
> FROM biblio.record_entry where tcn_value like 'cis%');
> 
> ~James Fournie
> BC SITKA Team
> 
> On Wed, Oct 22, 2008 at 1:54 PM, Grant Johnson <FGJohnson at upei.ca> wrote:
>> I don't want to change the table constraints in Evergreen.
>> But have a training opportunity for someone. :-)
>>
>> We need to delete some electronic records without items from the 
> catalog.(for now).
>> These records have a biblio.record_entry.tcn_source of LIKE 'cis'.
>>
>> I can't delete from biblio.record_entry as it's referenced as a foreign_key 
> in .metarecord.
>> I can't delete from .metarecord as it's referenced as a foreign key in 
> .metarecord_source_map.
>> I CAN remove the rows one at a time from the msm -> m -> re and the record is 
> gone.
>>
>> Can someone help with the syntax on how to batch delete the following..... 
> and let me know if this is all wrong!
>>
>> I know what this means - and am looking up how to build the syntax tonight:
>>  - ERROR:  more than one row returned by a subquery used as an expression... 
> :-)
>>
>> # DELETE from metabib.metarecord_source_map  where source = (SELECT id FROM 
> biblio.record_entry where tcn_value like 'cis%');
>> # DELETE from metabib.metarecord where master_record = ((SELECT id FROM 
> biblio.record_entry where tcn_value like 'cis%');
>> # DELETE from biblio.record_entry where tcn_value like 'cis%');
>>
>>
>> Table References:
>> biblio.record_entry
>> id = 14497
>>
>> metabib.metarecord
>> id=17448     master_record=14497
>>
>> metaib.metarecord_source_map
>> id=14710      metarecord=17448    source=14497
>> --
>>
>> F. Grant Johnson
>>  Systems Coordinator
>>  Robertson Library
>>  University of Prince Edward Island
>>
>>