check record dependency before deleting

[dennisome]

Hi all!

Please help me on this...

I would just like to create a method that checks if a record or records are being used in another table(s). We're using EJB3, JBoss Framework and MySQL in the development.

Currently, our implementation is using a try catch. When trying to delete the record returns an exception, which is commonly about the record is being used in another table, it immediately stops the current thread and prompts the user that the record is being used in another table.

Thanks,
Dennis

[Eranga]

So do you looking to use a SQL query to find this?

[dennisome]

if you can give me, please, why not.

[masijade]

Setup your foreign keys properly.

[dennisome]

the foreign keys was set to 'restrict' on delete. what should it be?...
'no action'...?

[masijade]

No, that is correct. That means you should get an exception if you attempt to delete a record that others are dependent upon. simply catch the Exception and check that it is that exception that was raised, rather than informing the user immediately. If you want to inform the user, simply keep a list of all the "records" that caused the exception and show it to the user at the end.

Edit: That will take less time than a possibly very large and time consuming join query with it's round trip to check the results.

[masijade]

And I know the try catch is what you already had, I was stating to you to kepp doing it that way, as that is the proper way. A query beforehand is a (possibly very) large amount of unneccesary overhead.

[dennisome]

That is good to know, masijade. But you see, the exception error also results an error message, "Transaction has failed," that goes with the message that is normally the only message I want to show up when deletion fails. I'm using richfaces message (<rich:message>) you know.
I believe this will be a JBoss richfaces question, but if you have any idea, please let me know... Is there a way to remove that exception message?

[masijade]

Well, you are already catching the exceptions, right, then adding them to this message (however you may be adding them). Well, use getErrorCode() from the SQLException and determine whether it was that exception that was thrown before adding the exception to the message.

If you are not using try catch inside the method doing the delete (but rather just throwing the exception and letting a generic routine handle it) then change that method from:

  public Whatever whateverYouCallIt(Whatever whatever) throws SQLException {
    // whatever
  }

to

  public Whatever whateverYouCallIt(Whatever whatever) throws SQLException {
    try {
      // whatever
    } catch (SQLException sqle) {
      if (sqle.getErrorCode() != whateverTheErrorCodeForTheForeignKeyConstraintIs) {
        throw sqle;
      }
    }
  }

[dennisome]

Thanks, masijade! That really helps a lot!...:)