Using Unique Key in SQL with or without try/catch as a valid way to verify integrity of data

Question

I have a table with some columns and I've set Unique Key on 3 of those columns to make sure there are no duplicates in the table. Now I was wondering if using try / catch to swallow exceptions throw on duplicates and continue updating inserting next rows in foreach is a good way to go?

            try {
                sqlWrite.ExecuteNonQuery();
            } catch (SqlException sqlException) {
                if (!sqlException.ToString().Contains("Violation of UNIQUE KEY constraint")) {
                    MessageBox.Show("Error - " + Environment.NewLine + sqlException.ToString(), "Error SQL");
                }
            } catch (Exception exception) {
                MessageBox.Show("Error - " + Environment.NewLine + exception.ToString(), "Error SQL");
            }

Or should I do SELECT inside insert query to check if row exists and if it does skip insert? I have read that it's not good to use exceptions as part of your data verification but some things are supposed to be used like that (for example How to check For File Lock in C#? is supposed to be used with try/catch).

http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/ — Aaron Bertrand, Apr 10 '12 at 16:08

JotaBe · Accepted Answer · 2012-04-10T15:25:57.270

1

You should never throw and catch exceptions if you have an alternative way to do it. ANd you now you have it. Use it!

You can create a single sql script or stored procedure that checks and inserts with IF NOT EXISTS(SELECT ...). Making two queries is slower.

edited Apr 10 '12 at 15:25

answered Apr 10 '12 at 15:06

JotaBe

38,030
8
98
117

He's not actually throwing the exception, he's catching it. I do agree though, Exceptions are for Exceptional circumstances :) – Mathew Thompson Apr 10 '12 at 15:08
He is not explicitly throwing it, but it's indirectly throwing it... Just a shade of meaning :) – JotaBe Apr 10 '12 at 15:15
hehe just nitpicking :), he's CATCHING it, not throwing it, directly or indirectly :) – Mathew Thompson Apr 10 '12 at 15:16
Thanks. I will add IF NOT EXISTS and leave try/catch anyway just in case there will be multiple people inserting things at same time. – MadBoy Apr 10 '12 at 15:20

score 0 · Answer 2 · answered Apr 10 '12 at 15:09

I would generally say that the primary key relationship should be the absolute last and fall back position for data verification. If your program has gotten that far and is trying to insert a row that fails unique key, something has gone very wrong.

In the most simple case, it's not too bad. For inserting a single row, without any other dependencies, you can make an argument that trying to insert the record and throwing a caught error might be more efficient then doing a select to see if the ID already exists.

Now, think about a transaction where you might be inserting/updating 10s or even hundreds of rows at a single time. Every one of the rows has a unique key, and any of them might throw an error. Then the DB has to roll back the whole transaction when an error is caught. Sure C# HAS a transaction mode, but you then have to roll back the DB and roll back any objects that might have been updated when inserting the records. It get's very complicated very fast.

Thanks for this. That's why I will leave try / catch anyways but do it thru IF NOT EXISTS that JotaBe suggested. — MadBoy, Apr 10 '12 at 15:44

Using Unique Key in SQL with or without try/catch as a valid way to verify integrity of data

2 Answers2

Linked