Unique Key violation using Many Valued Associations

BIZ TMS Aurelius
1

We got an unique Key violation on Many Value Associations, removing an Entry and Adding a new Entry (with the same Values in the Unique-Key Fields).

type
  // Automapping is enabled
  TChild = class;

  [Entity]
  TParent = class
  private
    FId: Integer;
    FChilds: Proxy<TList<TChild>>;
    function GetChilds: TList<TChild>;
    procedure SetChilds(const Value: TList<TChild>);
  public
    constructor Create;
    destructor Destroy;

    property Id: Integer read FId write FId;
    property Childs: TList<TChild> read GetChilds write SetChilds;
  end;

  [Entity]
  [UniqueKey('Parent_ID, Dummy')]
  TChild = class
  private
    FId: Integer;
    FParent: Proxy<TParent>;
    FDummy: string;
    function GetParent: TParent;
    procedure SetParent(const Value: TParent);
  public
    property Id: Integer read FId write FId;
    property Parent: TParent read GetParent write SetParent;
    property Dummy: string read FDummy write FDummy;
  end;

Method:
var
  hManager: TObjectManager;
  hParent: TParent;
  hChild, hNewChild: TChild;
begin
  hManager := TObjectManager.Create({Connect here});
  try
    hParent := hManager.Find<TParent>(1);
    Assert(Assigned(hParent), 'Parent not found');

    hChild := hParent.Childs.ExtractAt(0);
    Assert(Assigned(hChild), 'Child not found');


    hNewChild := TChild.Create;
    hNewChild.Dummy := hChild.Dummy;

    hParent.Childs.Add(hNewChild);

    hManager.SaveOrUpdate(hParent);
    hManager.Flush(hParent);
  finally
    hManager.Free;
  end;
end;

As far as I can see, the new Entity is Inserted before the old one is deleted.
That's why the UK-vio appears here.

2

Correct, first new collection items are added, and then the removed items are deleted (or unassociated from parent).
If you want to make sure to first remove and then add, call hManager.Flush(hParent) right after extracting the item from the list.

3

The given example just simplifies our problem.

In real we got an Entity like TParent putting Childs into an AureliusDataset.
The Dataset records are set by using SetSourceList.
There is no Manager attached to the dataset, so the changes will keep local.
That's necessary because we only want to save the changed data after pressing button Save.

Save will just save TParent with all changes appeared meanwhile within the Dataset.

That's why flush does not help, because new TChilds already appeared...

Are there any reasons why you first add new items before removing old ones?

Just tested:
Setting Unique Key to deferrable initially deferred will help if CascadeTypeAll is set.
But it won't help if CascadeTypeAllRemoveOrphan is set.

4

Historically, it's been always like this since version 1 released 10 years ago. Honestly, I don't remember right now why it was implemented like that, but I'm sure there were reasons. :slight_smile:

We can't change it. What we could consider is a property in the manager that changes this behavior, so you can set it to modify the existing default behavior.

This doesn't make much sense to me, because in either case (deleting the record in case of RemoveOrphan or setting the FK to NULL in the case of "no" RemoveOrphan) the processing of removed items occur at the same time.

5

This would help me. So please provide such a property.

Same for me. I just wanted to check, if deferrable would help.
After I recognized, that it would be a fine solution, I recognized, that some orphaned Entities appeared here.
Next step I changed CascadeType for ManyValueAssociation to CascadeAllRemoveOrphaned and the Error occurs again.
Taking a look into the database, Unique-Key Definition did not change, so it's still deferrable initially deferred.

6

I kindly ask you that you create a feature request for it, so we (you and us) can track its development:

7

Done:

1 Like
8

are you able to recreate the CascadeTypeAllRemoveOrphan error?

9

No, do you have a sample project displaying the issue? Will it be relevant if the requested property is implemented?

10

I just build a little sample only using Aurelius and the error with CascadeTypeAllRemoveOrphan does not occur at all.

I'll try to set up a little RemoteDb-Server for this sample, maybe it only occurs if you are using RemoteDb...

1 Like