CODEWITHSUNDEEP
delphifiredacon-duplicate-key
Atreide
Atreide

Reputation: 267

Duplicate key error when updating key field in a set of records

I have a master detail relation between two tables. Troq is the master table and Troq_Alma is the detail table. Troq_Alma primary key is formed by Troq primary key (Troq.cod) and "Num_Orden". This "Num_Orden" field is not only part of the PK of the table but is the priority of that specific Alma out of the several Almas for one Troq. (Troq_Alma is the table that relates the different Troq's with their related Alma's)

And at a certain point, the user needs to modify this priority therefore modify the primary key of a set of records (The set of almas related to one specific troq). In the form there are two buttons (Spinbuttons) related with one TDBGrid. If I push the "Up" button, selected Troq_Alma should decrease "Num_Orden" by 1 and the Troq_alma with that value in "Num_Orden" would increase its value by 1. So in the "image" we have of the table in the dbgrid, there is no duplicate key.

Naturally when I do Apply this updates: TFDQ_Troq_Alma.ApplyUpdates(-1);

I get duplicate key error which I find very logical as in any way that firedac would try to make this update, the first modification is going to throw duplicate key error until the amendment is made for that other record that previously had that primary key, being the fact that it still has that primary key.

I really don't know if there is any "fair" solution to this problem, the only thing that I imagined was to first add some amount to all "num_orden" for one specific Troq do the update and then update again to the originally modified values, which is a really odd job, but, on my short knowledge of delphi and firedac, I really don´t appear to find any other way to solve it.

Working on Delphi XE6 with cached Updates against Postgres 11.8 Database with firedac.

In case it could be of any interest, here is the code for both spin buttons (Up and Down):

procedure TFRM_Mant_TROQ.SpinBut_Troq_AlmaDownClick(Sender: TObject);
var iValActNumOrd, iValNumOrd2 : Integer;
    RegActual: TBookMark;
    iValMax: Integer;
begin
    RegActual := DM_Mant_Troq.FDQ_Troq_Alma.GetBookmark;
    iValMax := DM_DatosComun.MaxVal_FDQ(DM_Mant_Troq.FDQ_Troq_Alma, 'num_orden');
    DM_Mant_Troq.FDQ_Troq_Alma.GotoBookmark(RegActual);
    iValActNumOrd := DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value;
    if iValActNumOrd < iValMax  then begin
        DM_Mant_Troq.FDQ_Troq_Alma.Next;
        iValNumOrd2 := DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value;
        DM_Mant_Troq.FDQ_Troq_Alma.Edit;
        DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value := iValActNumOrd;
        // DM_Mant_Troq.FDQ_Troq_Alma.Post;
        DM_Mant_Troq.FDQ_Troq_Alma.GotoBookmark(RegActual);
        DM_Mant_Troq.FDQ_Troq_Alma.Edit;
        DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value := iValNumOrd2;
        DM_Mant_Troq.FDQ_Troq_Alma.Post;
    end;
end;

procedure TFRM_Mant_TROQ.SpinBut_Troq_AlmaUpClick(Sender: TObject);
var iValActNumOrd, iValNumOrd2 : Integer;
    RegActual: TBookMark;
    iValMin: Integer;

begin
  RegActual := DM_Mant_Troq.FDQ_Troq_Alma.GetBookmark;
  iValMin := DM_DatosComun.MinVal_FDQ(DM_Mant_Troq.FDQ_Troq_Alma, 'num_orden');
  DM_Mant_Troq.FDQ_Troq_Alma.GotoBookmark(RegActual);
  iValActNumOrd := DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value;
  if iValActNumOrd > iValMin then begin

      DM_Mant_Troq.FDQ_Troq_Alma.Prior;
      iValNumOrd2 := DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value;
      DM_Mant_Troq.FDQ_Troq_Alma.Edit;
      DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value := iValActNumOrd;
      DM_Mant_Troq.FDQ_Troq_Alma.GotoBookmark(RegActual);
      DM_Mant_Troq.FDQ_Troq_Alma.Edit;
      DM_Mant_Troq.FDQ_Troq_Alma.FieldByName('NUM_ORDEN').Value := iValNumOrd2;
      DM_Mant_Troq.FDQ_Troq_Alma.Post;
  end;
end;

This code can probably be improved but as I mention, I believe this code is working alright, the matter on my point of view is on how to indicate firedac to "eventually avoid" this unique key validation.

Actually Troq.Cod, Num_Orden is not the primary key of the table Troq_Alma, but there is a unique index on this two fields and I really like this unique index for database integrity purposes.

Upvotes: 2

Views: 844

Answers (1)

Rob Lambden
Rob Lambden

Reputation: 2293

If we accept that you have a good reason to change a unique key then you need to ensure that you avoid a key violation. It looks like you are working with data cached locally in a TFDQuery and then applying the updates.

The problem with that approach is that the TFDQuery remembers the loaded value of the field and the current value, so your intermediate changes are lost, resulting in a key violation.

A quick way to avoid that problem is to ApplyUpdates after every change. This could be a problem for you if you are remote from the main store.

If you need to assign a temporary value to an indexed field, you need to ensure that that value is unique. If the data schema allows it you could just negate the value of the Integer field (if it's not defined as unsigned). Although there could be another record with that value, if your convention is to only allocate these values temporarily you should avoid a key conflict.

Upvotes: 3

Related Questions