How to delete record from an entity with many to many relationship?
Question
Application Type
Reactive
Service Studio Version
11.11.6 (Build 44614)

Hello. In my data model, I use an entity (Dispersion) that is linked to another entity (UsedSolvents) in a many-to-many relationship.

 I have added a delete function to the dispersions screen. Currently, an error appears, probably because I have to delete the solvents assigned to the dispersion in addition to the dispersion.

Assuming I want to delete the dispersion with ID=2, I probably have to delete the solvents assigned to ID=2 at the same time to avoid an error message. Correct?

If I understand it correctly, I would first have to delete the solvents stored in the "UsedSolvents" entity in a server action using the "DispersionID" and then delete the dispersions associated with the "DispersionID" from the "Dispersion" entity.

In principle, I would need a function like "DeleteUsedSolventsByDispersionID". However, only "DeleteUsedSolvents" is available.

How can I delete the solvents from the entity "UsedSolvents" using the "DispersionID"?

Thanks a lot for your help.

Best regards,
Michael

Solution

Don't get me wrong, just trying to help. But what you described just now is an N: N relationship.

A dispersion can have any number of solvents. 1:n
The same solvent can be used in different dispersions. 1:n

The two together make N:N in this situation I would create a third entity to be N:N.

I will see you answer now, sorry

Kind regards,

Márcio

mvp_badge
MVP
Solution

Ok, so in that case, your "UsedSolvents" entity is the associative entity (a.k.a. linking table) between a Dispersion and a ComponentSolvent. That's fine, but you should remove the UsedSolventsId from Dispersion, as that is a 1:n relationship.

Hey there,

You can use the delete rule of the foreign key on the solvents, did you tried?

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Data_Modeling/Entity_Reltionships/Delete_Rules

Let me know if I helped you!

Kind regards,

Márcio

I have changed the deletion rule from "UsedSolventID" to "delete" as suggested.

If I now delete a record from the entity "Dispersion", the following error appears:

The DELETE statement conflicted with the REFERENCE constraint "OSFRK_OSUSR_YYY_USEDSOLVENTS3_OSUSR_YY_DISPERSION4_DISPERSIONID". The conflict occurred in database "DB_Outsystems_dev_SQL", table "dbo.OSUSR_YYY_USEDSOLVENTS3", column 'DISPERSIONID'. The statement has been terminated.

In this way, you should put the property delete on the DispersionID of the UsedSolvents Entity. Because you are trying to delete a record of the Dispersion.

Can you try, please?

Kind regards,

Márcio


mvp_badge
MVP

Hi Michael,

That doesn't look like a n:n relationship, more like a weird linked list of some sort. A proper n:n relationship should have two entities without foreign keys, and a third, associative entity that has two foreign keys, one to each of the two entities.

In your data model, a Dispersion can have one and only one UsedSolvents record associated (which makes it a 1:n relationship), and a UsedSolvents can have one and only one Dispersion record associated (which needn't be the Dispersion that links to the UsedSolvents!). So I think you need to remodel your data model first.

I notice that too, thank you, Kilian Hekhuis, for the explanation! That is just 1:N from the solvents and 1:N from the dispersions, there will be something breaking if you don't isolate that in a table like Kilian said and explained.


Hm, maybe I got the terms mixed up. What do I want to achieve?

A dispersion can have any number of solvents.
The same solvent can be used in different dispersions.

My understanding is that this works with the data model shown.

When I assign a new solvent to a dispersion, the DispersionID and the corresponding ComponentSolventID are stored under a new ID UsedSolvents. If a dispersion has several solvents, the DispersionID appears several times with different ComponentSolventIDs (see picture).

DispersionID=2 has a total of 2 solvents (ComponentSolventID 1 and 2). DispersionID=1 has only one solvent (ComponentSolventID 1).

Solution

Don't get me wrong, just trying to help. But what you described just now is an N: N relationship.

A dispersion can have any number of solvents. 1:n
The same solvent can be used in different dispersions. 1:n

The two together make N:N in this situation I would create a third entity to be N:N.

I will see you answer now, sorry

Kind regards,

Márcio

mvp_badge
MVP
Solution

Ok, so in that case, your "UsedSolvents" entity is the associative entity (a.k.a. linking table) between a Dispersion and a ComponentSolvent. That's fine, but you should remove the UsedSolventsId from Dispersion, as that is a 1:n relationship.

Thanks a lot. You put me in the right direction to have a real N:M relationship and to proper delete a dispersion.

Glad we helped you!! Happy coding! :)

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.