Checking for unique values from mySQL DB

I have a form that has several field that each one of them have to be unique by itself. what is the best way to do this query?

Should I open one connection and one mySqlAdapter and do:

myDataAdapter.Fill(myDataSet, "table"); 

for each value (cha开发者_开发问答nging the sql query each time) or is there a more optimized way to do this

Thanks in advance

Doron Sinai

---

Sounds like an unusual requirement. If you could elaborate more on the specifics, a better solution might suggest itself.

Given what you have said, I understand you need to know whether the entered value of any of several input fields on your form exist in "table" already.

Two options present themselves:

1) Ask the DB

Something like

SELECT column1, column2 FROM table WHERE column1='value1' OR column2='value2'

If you don't get any rows back, all values were unique (note though that another app user could enter the same values right after the SELECT... dealing with that is the subject of another question if you need help there).

If you do get rows back, the values in the returned rows will tell you what was already present in the DB.

2) Have the DB enforce uniqueness

Create a unique index on each column that must be unique. Note that placing a lot of indices on a database can significantly slow performance of inserts and updates and consumes disk space.

Note that option 1 might end up being too slow (depending on your DB size and hardware) without having an index on the fields you need to be unique.

---

In addition to the options Eric pointed out, I'll present another option. You could load up all of the existing values using one database call. You probably want to load the unique values into a HashSet for performance reasons. Then, check the form values against the HashSet and make sure those values don't already exist in the HashSet.

Here's the thing. You need to take into consideration how much data you're talking about here. If the data needs to be unique across the entire database table, you'll probably want to go for the Unique constraint Eric suggested. You don't want a scalability problem in the future by going with the approach I mentioned above, or by building a potentially poorly-performing SQL query which Eric also mentioned.

The right answer depends on the structure and amount of data for which you need to enforce uniqueness.