Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005

    Unanswered: Corrupt Index causing Constraint Errors:

    Setting Up the Question

    database: 1.4
    ASE : 5.5

    I have a legacy application written in Powerbuilder that has been choking recently on a few client machines. I chased down the cause, which was a Unique constraint being violated during an insert.

    This was actually a fairly difficult problem to identify in that probing the database for the ID yielded no pre-existing record that would clash, yet when trying insert a record with that ID, I got the error.

    On a hunch, I changed my query from "
    select * from T where ID = x
    select * from T
    and found the ID in the list! After doing some research, I found an article explaining that this behavior can be caused by a damaged table index. So I ran dbValid - c on the database and found my table in the following error list:

    Adaptive Server Anywhere Validation Utility Version
    Validating atp.Event_comments
    Run time SQL error -- Primary key for "Event_Description" has missing index entries
    The Question:
    How do I rebuild the index on this table to "unhide" these records? What I've done so far is create a script to read all the rows from the database, generate INSERT Statements and write them to file, then I delete * records from the table and reinsert the records from the file. All the records seem to be accessible now, but I'm wondering if this left holes anywhere.

    This version of the database doesn't seem to have any commandline utilities that can rebuild the indexes. Can anyone recommend a good way to do this without having to introduce coded-solutions.

    Also, does anyone have an iSQL script that can copy the contents of any given table into a temp table, then delete the contents of said table then repopulate from the temp table in one batch file or stored procedure?


  2. #2
    Join Date
    Mar 2001
    Lexington, KY
    Rebuild an index is simply drop/create


  3. #3
    Join Date
    Sep 2005

    Your're a man of many words

    Thanks for the verification on that. I wound up dropping then re-creating the tables along with their indexes and FK's. Now on to more interesting things

Posting Permissions

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