Fix “Key Violation” on Paradox tables.

“Key Violation” errors occur whenever a new record is created with a primary key that already exists. This means that the field you defined as your primary key must be unique on the table. No other record can have that value, and it generates a “Key Violation” error when this happens.




If you are not using Auto Increment fields just make sure the primary key does not exist and you are good to go.

This error is common on tables that use Auto Increment fields on the primary key.

To fix this, the AutoInc record that is hard-coded in the paradox table needs to be altered.

I  developed a simple application that analyses every table in a given directory, checks the AutoInc field and automatically repairs them.

If you like my free work, please consider making a donation and help me pay for the hosting and all the coffee and beer I drink developing this free software.

Paradox AutoInc Repair

Paradox AutoInc Repair (560.99 kB)

 

And here is the delphi code if you want to do this using  your delphi programs.

This function will allow you to set your AutoInc field to the specified value :

function SetAutoInc(FileName: TFileName; NewValue:Longint): Boolean;
begin
 with TFileStream.Create(FileName, fmOpenReadWrite) do
 try
 Result := (Seek($49, soFromBeginning) = $49) and (Write(NewValue, 4) = 4);
 finally
 Free;
 end;
end;

Filename is the table you would like to set a new AutoInc value.
NewValue is the new AutoInc value you would like to assign.

To reset a tables AutoInc you just need to call the function like this :

SetAutoInc('c:\mytable.db',0)

If you need to read the current value of the AutoInc field (not the value of the last record) you could use this function:

function ReadAutoInc(FileName: TFileName): Longint;
var
  st : TFileStream;
  Buffer : Longint;
begin
  st := TFileStream.create(FileName,fmOpenread + fmShareDenyNone);
  st.Seek(73, soFromBeginning);
  st.ReadBuffer(Buffer, 4);
  st.Free;
  Result := Buffer;
end;

This function returns an integer number with the current AutoInc value hard-coded into the table.
Call the function like this :

curAutoInc:=ReadAutoInc('c:\mytable.db');

Thats all folks…………..

This entry was posted in Algorithms, Delphi, Paradox.

12 Responses to Fix “Key Violation” on Paradox tables.

  1. Pingback: How to repair a Paradox table? « Delphi « tek.readers

  2. John Vaughn says:

    Just wanted to say thank you for the Paradox AutoInc Repair program. Every once in a while I have to repair some paradox tables for a client, and it is a bit of a pain with Database Desktop. This is very fast and easy. Thanks again!

    John Vaughn
    Houston, Texas

  3. Domen Grabec says:

    Woow!

    This solved my problem out of the box. Just needed to follow theese steps to make your app work:
    https://prometheus.atlas-sys.com/display/illiadkb/Borland+Database+Engine+(BDE)+$210D+error

    U are a life saver tnx 🙂

  4. Shane Fox says:

    Thank you so much. Your program is quick and amazing. Only took about 30 seconds to fix my Paradox Key Violation errors.

  5. Bruce Waldie says:

    So elegant! I had knew that there was an offset into the header where the autoinc field was stored, but did not know where it was and how big it was. Your answer is that it is at offset $49 (73) and that it is a LongInt. While I have tried your program and then used Paradox and I can now add records, I still need to try it in my client’s actual Delphi program (which I wrote) and make sure all the other related tables work ok. I am very sure they will work.

    I had tried several other solutions and none of them worked. One offered was to change the + field to I, save the file, and then change it back to +. It didn’t work because there was a dependent field based on the primary index built on the + field in the secondary table and Database Desktop would not allow it. I bought software from a company in India who promised that they could fix it, but they couldn’t. They did give me a refund.

    I hope to be able to check your fix within the next two days in the real environment, but I am sure it will work. I will let you know.

    • Bruce Waldie says:

      Brilliant! Finally got the chance to test it in the real environment and it worked flawlessly. It even allowed the recovery of some information in linked tables that had been entered for that one main record. Is there a way I can send you a Christmas present?

      • I am very glad it works for you guys. There is no need for a christmas gift.
        Knowing that this was usefull for someone is enough for me.

        • michael says:

          hi
          i hv client server tech software running in a hospital.
          whenever i do more than two different medicine entry billing, the software gives key violation pop up.

          kindly help.
          michael

  6. easy says:

    Thx mate..finaly I found one click app that works..
    This save me lot of nervs and work!
    Thx again!

  7. Marcelo says:

    Interessante e muito prático. Parabéns e Obrigado.

  8. Patrick says:

    For years I have struggled with this hugely complex program (way too many interdependent files) and at least once a year I have this issue. Until now I had to work from a backup to get it fixed. Took me forever each time.
    Now this little tool does it in seconds. Brilliant in all it’s simplicity.
    Many many thanks

  9. Horst says:

    How do you find the value that you need to set for the AutoInc (while doing the repair)?

Leave a Reply

Your email address will not be published. Required fields are marked *

Please calculate the arithmetic expression to validate you are human. *