Conditional insert vs UNIQUE constraint
It depends on what you are needing from the solution. If you want to 100% prevent duplicate errors you go with a constraint. If it is to minimize duplicate rows and prevent errors from happening you go with the conditional insert. If you need to prevent duplicate rows and the errors from attempting to insert a duplicate row you go with both.

If you are depending on the conditional insert you can get a duplicate row if someone is doing an insert and they do not add the condition as expected. 

If you just use the constraint you will get errors that could be prevented (sometimes you want to know of the error) when attempting to insert the duplicate record.