Preventing Orphan Records in Microsoft Power Platform Functional Consultant Exam
Question
You create a many-to-one relationship between records in table A and table B in the Dataverse database.
Then, in a model-driven app, you delete the record from table B.
But you still can see the “orphan” records from table A.
You need to prevent this type of relationship's behavior that creates “orphan” records.
Please select the option that would prevent deletion of the parent record that has related child records.
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D.Correct Answer: D
Dataverse defines the two types of relationships between tables: One-to-Many (1:N) and Many-to-Many (N:N)
A one-to-Many relationship is a Parent-Child relationship, like between a person and multiple email addresses.
One person can have multiple email addresses.
The person record is related to the multiple records in the Email table from the Person table point.
It is a One-to-Many relationship for a person record.
For the related email record, this relation is Many-to-One.
When you create such a relationship between tables, the Dataverse automatically adds a Lookup column in a child or related table.
When you relate two tables in the Many-to-One or One-to-Many relationship, you need to instruct the Dataverse what to do when you delete the records from the parent table.
There are three advanced options for relationship behavior: Referential, Parental, and Custom.
Referential type preserves the child records from the actions on the parent records.
It has two options: Remove Link and Restrict.
If you use a Remove Link option, you preserve the child records when a parent record is deleted.
If you use a Restrict option, you prevent the deletion of the parent records related to child records.
/answer/imgckeditor_2_42_31.jpg)
Option A is incorrect because the Parental type defines the child records' deletion when you delete the parent record.
It does not prevent the deletion of the parent record when the related records exist.
Option B is incorrect because the Referential Remove Link type defines the parent record's deletion without deletion of any related child records.
It does not prevent the deletion of the parent record when the related records exist.
Option C is incorrect because Custom Cascade All defines the child records' deletion when you delete the parent record.
This behavior is the same as the Parental type.
It does not prevent the deletion of the parent record when the related records exist.
For more information about Dataverse table relationship behavior, please visit the below URLs:
The correct option to prevent the behavior that creates orphan records when deleting a parent record that has related child records in a many-to-one relationship is D. Referential Restrict.
When a many-to-one relationship is created between table A and table B, it means that each record in table A can be related to many records in table B, but each record in table B can only be related to one record in table A. This relationship is established through a foreign key in table B that references the primary key in table A.
When a record is deleted from table B, the relationship between the record in table A and the deleted record in table B becomes invalid. This can lead to orphan records in table A, which are records that have no related record in table B.
To prevent this behavior, you need to set the Referential Restrict option. This option ensures that you cannot delete a record from table B if there are related records in table A. In other words, you cannot create orphan records in table A by deleting records from table B.
Option A, Parental, is not a valid option for preventing orphan records. This option would prevent the deletion of the parent record even if there are no related child records, which could cause data inconsistencies and unnecessary data storage.
Option B, Referential Remove Link, would delete the relationship between the records in table A and B when a record is deleted from table B, but it would not prevent orphan records in table A.
Option C, Custom Delete Cascade All, would delete all related records in table A when a record is deleted from table B. This would prevent orphan records in table A, but it could also lead to data loss and is not recommended unless it is necessary for your specific use case.
Therefore, the correct option is D, Referential Restrict, which prevents the deletion of records from table B when there are related records in table A and ensures that no orphan records are created in table A.