The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Testing to see if a database row is deletable before a delete

Hi

I want to test an arbitrary row to see if the database will allow me to delete it (i.e. no Foreign Key violations).  This is BEFORE I actually delete it.

The reason being, I want to be able to prevent the user deleting this row from a set in memory if a Foreign Key will prevent the row from actually being deleted in the database.  Additionally, I'd like to produce a meaningful message to the user like:

 "You can't delete a X when it is being used in a Y".

I've thought of retrieving the foreign key info for table X, checking to see if there are any ON DELETE RESTRICTions and then checking to see if there are any matching rows in table Y.  This just seems cumbersome and slow to do.

Or for a WTF idea: inside a transaction, I delete the row then rollback the transaction - on an exception I interrogate the exception to see what foreign table is preventing the delete.

Any (more sensible) ideas?
Paul Norrie Send private email
Thursday, September 04, 2008
 
 
Well, assuming that you have one foreign key, how about writing your select as

SELECT p.*, c.a AS marker
FROM parent p,
LEFT JOIN (SELECT DISTINCT parent_id FROM child) AS c
  ON p.id = c.parent_id
WHERE ...

Then you could look at where the marker was null to know whether the row was deletable.

A kludge, and subject to at least the problem of a row added to "child" after the query and before the delete. But you could try it.
George Jansen Send private email
Thursday, September 04, 2008
 
 
Where does the 'a' come from in the expression 'c.a'?
Where's my morning coffee?
Friday, September 05, 2008
 
 
I haven't thought his through.  On the face of it, it sounds like you are trying to have your cake and eat it, too.

The big benefit you get from referential integrity constraints in the database is that you get to let somebody else put in the constraints and enforce them,  while you only react appropriately to errors when they arise.  (Someone else may be yourself, wearing the DBA hat instead of the programmer hat,  but you get my drift.)

The big benefit you get out of haing a database in memory, aside from speed,  is that you have complete local control over the data.  Local control impplies local responsibility. 

Let me ask another question.  Is the set of rows you keep in memory the total set  (closed world assumption)?  Or is it merely a "working set" that includes recently used rows from the database table  (open world assumption in memory,  closed world assumption in the database)?

If it's the latter, why not just go ahead and delete the row from memory but not the database?  If the same row is needed later on,  it will be fetched into the working set
again.  If it's never needed again,  the normal case,  it will just lie dormant in the database.

Or do you want the user to be able to declare the entry invalid for future data validation,  using referential integrity?  In that case,  maybe you need a boolean column called "Invalid" in your rows,  and you need to write an on insert trigger for your referring tables that excludes invalid rows from consideration.  Now you can, on behalf of the user,  mark a row as invalid in the database without actually deleting it.

You can garbage collect the invalid and unused rows in some routine periodic cleanup process, in the context of a different transaction.
Walter Mitty Send private email
Friday, September 05, 2008
 
 
@coffee: Quite so. I was interrupted while writing the post but hit submit anyway.

for "c.a" read "c.parent_id"
George Jansen Send private email
Friday, September 05, 2008
 
 
> This just seems cumbersome and slow to do.

Why cumbersome? It's only one SELECT (or two, if you don't already have the metadata and need to select that too).

I don't know your schema, but constraints and triggers too can also prevent a deletion.
Christopher Wells Send private email
Friday, September 05, 2008
 
 
The SELECT option is duplicating the concept already contained in the constraint, which kinda sucks.

Suggestion, don't use a constraint per se, instead, use a "sp_delete" that takes a "mode" parameter, "really do the delete, or just validate that it can be done".

Probably not appropriate to some DB's.
Greg Send private email
Friday, September 05, 2008
 
 
Didn't notice this at first:

"I've thought of retrieving the foreign key info for table X, checking to see if there are any ON DELETE RESTRICTions"

Your code generator should do that.
George Jansen Send private email
Friday, September 05, 2008
 
 
Or for a WTF idea: inside a transaction, I delete the row then rollback the transaction - on an exception I interrogate the exception to see what foreign table is preventing the delete.
---
That's the cleanest and the most general method. However, why rollback the transaction if the delete succeeds?

Afterall, the user does want to delete the row and only needs to be informed when the delete fails (in which case the transaction would probably have been rolled back automatically by your dbase toolkit).
Mike Adewole Send private email
Saturday, September 06, 2008
 
 
Don't forget race conditions with advance checking:

1) Can I delete row 44321

2) Yes, answers the server

3) Another process creates a table entry which prevents 44321 from being deleted

4) Delete fails!

Point is, you can still get a failure even if you "checked".
What is this Web stuff, anyway
Monday, September 08, 2008
 
 
1.  Attempt to delete the row in the db.
2.  If the row is deleted, delete the row from memory
3.  If the row is not deleted (exception is thrown), prompt the user.

What am I missing?

As the previous commenter stated, it's impossible to be absolutely sure the delete will work without locking the record as well as preventing other inserts.
Michael Bacarella
Monday, September 08, 2008
 
 
Good point about the race condition!

I don't want to delete the row straight away because the whole application uses an edit-save cycle where the user explicity causes a save to happen (or else cancels).

Deleting the row immediately breaks that whole user expectation of save/cancel at a later date.  BTW - I'm not saying this is a good thing but it's what we are doing.
Paul Norrie
Wednesday, September 10, 2008
 
 
"Good point about the race condition!"

Yeah, don't try to pre-empt what the database does for you.
Scorpio Dragon Send private email
Thursday, September 11, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz