Once you know the rules, you set a UNIQUE constraint on the target table and stop worrying. However, the need to prevent attempt to enter duplicates on teh front end does exist. What I have been doing in cases like that is:

1) set up UNIQUE constraint on the table
2) do not check BEFORE you send the data to be inserted, let it go as is. Most of the time all will be well - people do not introduce duplicates too often. This way, most of entries (are not duplicates) will not be slowed down.
3) if we really have a duplicate, let SQL server react and capture the error (number, description). If the error says "Duplicate' then you may want to didsplay appropriate message to the client. Often, it is helpful to tell the user where the 'first copy' is. In that case you do teh search and send response to the user. This way, you do not do search on every entry, you do it only when there is attempt to insert duplicate.

The same tactic helps with avoiding FOREIGN KEY errors.
Protect the data on the table level, do not check data on insert, simply wait for response by teh system. Even if you do not know how to read the message from the server, duplicates will be prevented, no code needed.

The best code is no code at all.

🙂