deleting structure with cascading deletes?

Are thre any reasons why a cascading delete should be used to delete rows in a JChem structure table as opposed to using the UpdateHandler to specifically delete the rows?





Situation is that I have two tables xxxx and xxxx_mol which has a foreign key relationship defined between them using the cd_id column from the xxxx_mol table so that when I delete a row from xxxx the cascade delete also deletes the row from xxxx_mol. Anything bad about doing it this way?





Thanks





Tim

Hi Tim,





We recommend using UpdateHandler to delete row from the table for the following reason:





If you use structure cache to speed up the search process (which you should), the search algorithm must know about the changes made to the structure table to refresh the cache when needed, and thus provide correct search results.


The changes are properly recorded in the property table (e.g. JChemProperties) by all JChem API methods, that's why we suggest that all changes to the structure table should be made vie the API.


Only updates on additional data columns (user defined data fields) can be an exception.





In your case you would get invalid hits for structure searches on xxxx_mol, pointing to deleted records.





If xxxx is not a structure table, then you should use the arrangement in the opposite way (if your application allows it):


When you delete from xxxx_mol with UpdateHandler, the corresponding rows from xxxx will be deleted.





Is that a viable solution for you ?





Best regards,





Szilard

If you want to access JChem functionality using standard SQL semantics, consider using JChem Cartridge for Oracle (with regular tables). JChem Cartridge implements JChem-specific maintenance functions like update tracking behind the scenes using low-level programmatic "hooks" provided by Oracle.





Peter

Thanks. That's given me the info I need.





Tim