Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting
Let the DB raise an error. 

Testing first isn't safe for concurrency because you'll get a collision eventually because 2 threads may pass the "NOT EXIST" and both will try to write. This applies to both "READ COMMITTED" and MVCC/Snapshot lock strategies.

You can use lock hints to force isolation, but you reduce performance.

I call this the JFDI pattern (SO link). For "update if exists" then see this here: Need Help Troubleshooting Sql Server 2005 Deadlock Scenario. These are SQL Server. MySQL has INSERT IGNORE which handles this gracefully. Not sure about the rest