Enforcing unique columns

If a column is made unique in a database table structure, is there any need to do a check to see if a 开发者_开发问答new value to be inserted already exists in the table via script? Or would it be fine just to insert values letting the DBMS filter non-new values?

When you will try to insert a duplicate value in a unique column, your insert query will fail. So it might be a good idea to make sure you are checking to see if your insert queries went well or not. Althought regardless of the situation you should always check if your insert query went through or not :)

You should always validate your data before inserting it on the database. That being said, what will happen if you try to insert a non-unique value on a unique defined column is an SQLexception.

In order to validate this before insertion, you could for example do a

 select 1
 from mytable_with_unique_column
 where my_unique_column = myNewValue

If the query returns anything, then simply do not try to insert as that will throw an SQLException.

Verification of unique constraint is definitely an overkill.

When you put unique constraint on your column, an implicit index is created for this column. Thus, DBMS can (and will) verify your data much faster. Unfortunately, when you try to insert duplicate value into your column, you will get constraint violation exception you have to deal with (but you have to deal with such error while using script verification either).

Good luck.

You can combine the insert statement and validation select into one statement:

insert into mytable_with_unique_column (...) values (...)
where not exists
(
  select 1
  from mytable_with_unique_column
  where my_unique_column = myNewValue
)

This will only insert a new row if there isn't already a row with the given unique value.