How to avoid duplicate look up combination in many to many relationship?

Question

I am trying to learn many to many relationships for salesforce objects, I was able to implement it using a junction object where there are two master-detail relationships. But it causes a duplicate issue. For example, if I have three objects Student, Course, Course Student Relationship; using Couse Student Relationship object as junction object and Student and Course as master objects, I was able to assign multiple students in the same course and a student can take multiple courses. But it also allows me to assign the same student to the same course several times. What is the best way to solve it? I tried the validation rule but apparently, validation rule can only check if the lookup is populated or not. Is there any way to check and show error like validation rule without using trigger ?

Answer 1

You can write a before trigger on the junction object and check if the combination already exists. If yes then use addError to throw an error.

Answer 2

You can create a Unique field (Unique_Course_Student_Relationship__c) on the junction object and populate it with a concatenation of StudentId and CourseId through a record-triggered flow on create.

CASESAFEID(StudentId) + CASESAFEID(CourseId)

This will ensure that there's never two junction records with the same Student and Course as the value of that field needs to be unique and an error message will display and let the user know they're creating a duplicate of an existing Id.

duplicate value found: Unique_Course_Student_Relationship__c duplicate values on record with id: 000000000