Clarity on Delete rule constraints in Entities for best practise

Hi,

I was going through the performance best practice data model document.

https://success.outsystems.com/Documentation/Best_Practices/Performance_Best_Practices/Performance_Best_Practices_-_Data_model

Under the section "Use of the Delete Rules constraints in Entities" its written  like:-

This best practice applies to applications where the data is effectively deleted from the database and when this happens to entities which other entities depend on. One of three scenarios is possible:

1.Use the Protect rule, which means that your application must be delete all records from child entities first (or the database will return an error) and then when you delete the parent entity record, the database has to check if there are records in the child entities (which is unnecessary or will return an error). This is the worst option in terms of performance.

2.Use the Delete rule, which means that all control is given to the database (which has no business knowledge) and may be trying to delete things that were already deleted.

3.Use the Ignore rule, which may lead to data inconsistency if your application is not working correctly, but is the more performant option by far. If you use this option, it's fundamental that you have a housekeeping strategy on your DB to assure that you get rid of all unindexed data.

For example:There are 2 entities: A and B.The Entity B has a reference attribute to Entity A.

So, now applying the delete rule property for the reference attribute in Entity B.

So, for point no.1,the delete rule is set to protect, the records in Entity A will not be deleted if it is being referenced in records in Entity B.

So, what does this means in point no.1 "which means that your application must be delete all records from child entities first (or the database will return an error) and then when you delete the parent entity record, the database has to check if there are records in the child entities (which is unnecessary or will return an error)" that when we try to delete a record from Entity A, it will  first delete all referencing records in Entity B and then will delete records from Entity A and before doing that, it will check again in Entity B if there are still referencing records present?

 I thought it will first check if there are referencing records in Entity B to the record to be deleted in Entity A.If its there,it will not delete the record in A or else will delete.

Point no.2: What is it trying to imply exactly?


Please let me know if its not clear.

Regards,

Indranil

Hi,

The first point means that if you try to delete a record that is referenced in another entity the database will throw an exception. That means that you need to do the logic in your application in order to delete all records that reference that one.

The second point (delete rule) means that if you delete a record that is referenced in other entity it will delete also those records (cascade).


Hope this is clear now.

Solution

Hey Indranil,

First let's create a little example:

EntityA
Id
SomeAttribute

EntityB
Id
EntityAId
AnotherAttribute

In this example we have EntityB having a reference to EntityA, by EntityB.EntityAId.

If we setup the delete rule for EntityB.EntityAId on Protect and we try to delete the corresponding EntityA record the following will happen:

1) When running the delete query, the  database is looking up if the EntityAId, is being used by any other table as a reference, it finds a record in EntityB, where the EntityB.EntityAId has a delete rule of protect.

2) The database will throw an exception and will not delete the EntityA record.

3) The application which executed the query will get the exception and possible bubble up or catch this exception or display it to the user.

When using a protect rule, you as a developer will have to make sure you delete all references in other Entities of the EntityA.Id you wish to delete, before deleting the EntityA record itself.

 When using a delete rule, the database will do this for you instead.

When using an ignore rule, will leave the other entities intact, but will possibly NULL the referenced attributes (I'm not sure if it's nulled, or if it's not a hard foreign key to begin with).

In any case, when using an ignore rule you might need extra logic to keep the data in your database consistent, as the entities that reference EntityA might use keys that are not currently present in EntityA.

I hope this has become more clear for you, if not, feel free to ask any questions.

Solution

Joey Moree wrote:

Hey Indranil,

First let's create a little example:

EntityA
Id
SomeAttribute

EntityB
Id
EntityAId
AnotherAttribute

In this example we have EntityB having a reference to EntityA, by EntityB.EntityAId.

If we setup the delete rule for EntityB.EntityAId on Protect and we try to delete the corresponding EntityA record the following will happen:

1) When running the delete query, the  database is looking up if the EntityAId, is being used by any other table as a reference, it finds a record in EntityB, where the EntityB.EntityAId has a delete rule of protect.

2) The database will throw an exception and will not delete the EntityA record.

3) The application which executed the query will get the exception and possible bubble up or catch this exception or display it to the user.

When using a protect rule, you as a developer will have to make sure you delete all references in other Entities of the EntityA.Id you wish to delete, before deleting the EntityA record itself.

 When using a delete rule, the database will do this for you instead.

When using an ignore rule, will leave the other entities intact, but will possibly NULL the referenced attributes (I'm not sure if it's nulled, or if it's not a hard foreign key to begin with).

In any case, when using an ignore rule you might need extra logic to keep the data in your database consistent, as the entities that reference EntityA might use keys that are not currently present in EntityA.

I hope this has become more clear for you, if not, feel free to ask any questions.

Hi Joey,

Thanks a lot for such clear explanation :).Was little confused after reading the performance document.

Regards,

Indranil


Nuno Rolo wrote:

Hi,

The first point means that if you try to delete a record that is referenced in another entity the database will throw an exception. That means that you need to do the logic in your application in order to delete all records that reference that one.

The second point (delete rule) means that if you delete a record that is referenced in other entity it will delete also those records (cascade).


Hope this is clear now.


Hi Nuno,

Thank you for your response.Its clear now.

Regards,

Indranil