Is it enough to validate in the business logic before persisting or should i use triggers on top of that for important data?

Question

I have the following class model:

I must make sure that an employee only has one role belonging to a project. So if Project A has the roles roleA and roleB, then the employee is only allowed to have one of them(but hes obviously allowed to have a role from another Project, but again, only one).

I make sure that thats always the case in my business logic. So before i add a role to an employee, i check whether the employee already has a role which belongs to the project of the role he wants to add, etc. So using my API/business logic, im able to make sure that the must criteria is fullfilled.

But should i add an extra layer of security on the database level? I could add triggers which check the above mentioned criteria. That would make it absolutely impossible to add any data into the database which violates my criteria. Is that extra layer of security on database level needed or is it enough if i do the validation in my business logic? And is a trigger the correct best way to do it?

Edit:

I implement what the comments suggested the following way:

My IdClass implementation:

@Data
public class TestId implements Serializable {
  private Project project;

  private Employee employee;
}

The class implementing the ternary association, making the employee and project pair unique:

@Entity
@Data
@IdClass(TestId.class)
public class Test {

  @Id
  @ManyToOne
  private Project project;

  @Id
  @ManyToOne
  private Employee employee;

  @ManyToOne
  private ProjectEmployeeRole projectEmployeeRole;
}

Answer 1

There are two questions in one.

Database: If you want the database to enforce this rule, you do not need any trigger. Just implement the many-to-many relation between Employee and Project using an association table with a primary key made of EmployeeId and the ProjectId: the combination must be unique. In the association table you'd also store the sole role this employee would have on that particular project.

UML class diagram: your diagram is misleading, since there are a lot of many-to-many association, but unfortunately, you say that one particular role can appear only in one project. A clearer way to represent this in UML would be to represent a ternary association (the association table I mentioned for the database would in fact impelment this ternary association). Another variant would be to use an UML association class and this association class could itself have an association with the role.