Hi Graeme, I get around this by not having any Foreign Key Constraints. The Relationship Manager pattern can handle such referential conflicts without needing the need for the underlying database to supply them. I'm not a fan of Foreign Keys constraints in databases for my apps anyway. For example, it's quite easy to setup a cascading delete which makes it easy for your database and BOM to get out of synch. In effect, they're a sort of business rule and.. well you know the rest! ;) I keep my databases as simple as possible; No triggers, stored procedures or foreign key constraints, just tables, indices and a primary key constraint per table. It also has the added side effect of making your databases much more portable as you are much likely to be using anything vendor specific.
Regards, Andy -----Original Message----- From: Graeme Geldenhuys [mailto:[email protected]] Sent: 24 March 2011 10:33 To: tiOPF discussions Subject: [tiOPF-talk] How to manage a 'Foreign Key Constraint' error ondeleting Hi, Could somebody share how they handle foreign key constraints when trying to deleted data. For example: I my case I am importing data from an old system to a new system. Part of the data is School names. In the new system the Schools are in a lookup list (where the old system simply had a text field). This means many people spelled the same school differently, but with some data cleanup (a manual process only), this issue is avoided in the new system. So say somebody cleanup up some data, and "think" they got all cases covered. They now try and delete those old misspelled schools from the School Lookup List. They might have missed one case where the misspelled school is still used, and thus our program gives a ugly "Foreign Key Violation: foreign key references are present for the record" message. In the program I do handle this by restoring that record's data via a Memento object - thus the ObjectState etc is back to normal (not posDelete) even on a delete failure. eg: b := tiAppConfirmation(uiMsgDeleteConfirmation); if b then begin view := FMediator.FindMediatorView(Grid); lData := TSchool(TtiStringGridMediatorView(view).SelectedObject); if Assigned(lData) then begin m := lData.Memento; // allows for later undo try lData.Deleted := True; lData.Save; m.Free; DataList.Remove(lData); except on E: Exception do begin lData.Memento := m; // restore the object m.Free; tiAppError(E.ClassName + LineEnding + E.Message ); end; end; end; end; How do others handle such database constraints with tiOPF based applications? Basically I would like to give a more "user friendly" message like "This record is still in use, so can't be deleted as this time". I checked the exception class that was raised, and it is simply EIBDatabaseError. This doesn't say much about the specific database error that occurred, plus if we had to use a different persistence layer to Firebird (eg: PostgreSQL or Oracle), then that exception class would be different. So how best do we handle this? This question relates to any such "foreign key constraint" errors on deleting data, not just to my School List example. So I don't think it's viable mentioning that I need to iterate over my data *before* even allowing the delete to happen. Because that means I would probably pull in millions of rows of data to do such checks! Yes the School List example might be small, but other types of data might not. Regards, - Graeme - ---------------------------------------------------------------------------- -- Enable your software for Intel(R) Active Management Technology to meet the growing manageability and security demands of your customers. Businesses are taking advantage of Intel(R) vPro (TM) technology - will your software be a part of the solution? Download the Intel(R) Manageability Checker today! http://p.sf.net/sfu/intel-dev2devmar _______________________________________________ tiOPF-talk mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/tiopf-talk ------------------------------------------------------------------------------ Enable your software for Intel(R) Active Management Technology to meet the growing manageability and security demands of your customers. Businesses are taking advantage of Intel(R) vPro (TM) technology - will your software be a part of the solution? Download the Intel(R) Manageability Checker today! http://p.sf.net/sfu/intel-dev2devmar _______________________________________________ tiOPF-talk mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/tiopf-talk
