Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Error Handling in Foreign Keys

    Hi, I have a question related to error handling.

    Look at the following:

    Error Code : 1216
    Cannot add or update a child row: a foreign key
    constraint fails

    Error Code : 1217
    Cannot delete or update a parent row: a foreign key
    constraint fails

    I have a parent table which has more than one child
    table. When I catch the error in the programming
    language, I would like to know
    which table is giving the error because I want to
    display the Message Box with the corresponding
    message to the user, something like: "Cannot delete
    customer because invoices exists."

    Is there something in MySQL that can be able to tell
    me this?

    If there´s nothing to correct this, do you have an
    idea to resolve this with something else?

    Thanks for the time!
    Roland

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    since the mysql error message is not specific enough, you should run validation queries first to check that the intended operation is valid before doing an update/delete. that way you'll be able to present accurate messages to the user.

  3. #3
    Join Date
    Jun 2004
    Posts
    21
    Ok, that sounds good. I´ll do it.

    By the way, I don´t know if those validations
    would bring a little overhead before doing the insert/update/delete.
    What do you think about this If there are lots
    of users connected to the database?

    Thanks for the time
    Roland

  4. #4
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    a select (read only) query should be no trouble. if the tables contain more that 200 records, starting thinking about using indexes if you have performance issues.

    the thing is they are necessary from the application proint of view and should not be complex. to be safe, you could add a high level switch, using define(...), so that you can turn the extra validation off if the site has performance problems after introducing the change.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •