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
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:nThe 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
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!
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?
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).
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! :)