Delete Actions In Microsoft D365 F&O

Delete actions comes into the picture when there is a need to maintain the database integrity. It defines what will happen in another related table if we delete a record in a table.

By implementing delete actions, we ensure the data consistency in the related tables and avoid deletion anomalies.

There are three types of delete actions which we will explore one by one further:

1. Cascade
2. Restricted
3. Cascade Restricted

Let’s take an example of a scenario involving the Patient table, Billing Table, and Diagnosis Details table. We want the diagnosis details and billing information to be deleted whenever we are deleting a patient on the patient table.

To implement that, we will be using Delete Action of type Cascade. We will add the delete action of type cascade based on the relationship between the tables. As can be seen in Fig 1 & Fig 2.

Fig 2

Delete Action of type Cascade works in a way that when a record is deleted from one table, the corresponding related record in the related table is also deleted creating a cascading effect. It means, that deleting records from the parent table deletes the records in the child table too, on the relation of their primary and foreign key.

Now, let’s look at the second type of delete action which is Restricted. Taking an example of a table of Doctors and a table of Departments. We want the deletion of the department to be restricted if there is a doctor present belonging to that department.

Fig 3

Here, if a record in the parent table (SLD_Departments) is to be deleted, first it will check whether the record in the child table (SLD_Doctor) exists or not. If it exists, it will throw a message that the department can’t be deleted because there is a record present in SLD_Doctor.

Finally, there is a third type of delete action that is Cascade Restricted. It is a mixture of both types as the name suggests. Let’s take the example of SLD_Departments, SLD_Doctors, and SLD_DiagnosisDetails tables.

In this case, SLD_Departments is the parent table of SLD_Doctors. And, SLD_Doctors is the parent of SLD_DiagnosisDetails. So, we add delete action type cascade in between SLD_Departments and SLD_Doctors. And, add delete action type cascade+restricted between SLD_Doctors and SLD_DiagnosisDetails.

Now, if we delete the department then the corresponding records in SLD_Doctors will be deleted, and the corresponding records in SLD_DiagnosisDetails will also be deleted. But, if we delete the records in SLD_Doctors and the records are present in SLD_DiagnosisDetails then it will not allow the doctor to be deleted.

Conclusion

This article has explained the delete action types with basic examples. Delete actions are quite an interesting and important thing to configure between related tables to ensure data consistency and to avoid data corruption. Feel free to respond and share other solutions too.