Handling a violation of n multiple unique constraints in insert

Question

I have an asp.net mvc 5 site.

I have a places table / POCO with 3 unique constraints (URL, Email & obviously ID).

Users are able to add places - but they may violate URL or Email unique constraints, and I need to tell them why an insert failed.

I am tempted to just let the insert fail (by violating the Unique Constraint in the database).

I could do multiple round trips to the database to check for uniqueness - but that is slower, more work - and in theory creates a (very unlikely) race condition.

I would also like to tell the user (ideally avoiding technical error messages) - that URL - or Email - needs to be unique.

What is best practice here? What is quick & efficient? I am not using stored procedures on this site (instead parameterised sql via dapper.net) - and I would like to avoid sprocs but maybe thats the best way?

Answer 1

The main problem here is that you can't really make sure that an URL or an email isn't already in the database until you commit the insert.

The reason for this is that is that either the URL or the email might have been inserted into the database in the time between you make the check and you make the insert.

So the simpler design is to try to insert the record and if it fails then inform the user which of the URL or email is a duplicate and allow them to alter their data.