2

I have a table with has many rows, I want to set unique constraint for this table, but the column that must be unique, has data is not unique. Is any way to set unique constraint for this table for this column or unique constraint for any insert or update next time?

I can't delete data because is real

1
  • you have to delete that data first
    – Ganesh_Devlekar
    Aug 24, 2014 at 9:11

3 Answers 3

3

You can use a filtered unique undex to apply a uniqueness constraint to a filtered set of data. For example this: http://sqlmag.com/blog/whats-good-use-unique-filtered-index

For example you could exclude your non unique rows from the index and make it unique

But you really need to fix your data or it's going to cause you problems in future.

2

There are two ways:

  1. You can delete all the data for that row and then add the data again after applying the unique key constraint.
  2. You may try to create a temporary table and add your data in that table and then delete all the rows from your source table. And then put the unique constraint on your source table and put the data back using the temporary table.
2
  • is any way to not delete data and add trigger to check data after update or insert to avoid duplicate data?
    – asieh
    Aug 24, 2014 at 9:16
  • @asieh:- Yes you can but that would not be effective on the existing data. Also to add you may try to remove the duplicate data from you table.
    – R.T.
    Aug 24, 2014 at 9:17
1

If you are able to delete duplicated records or update them in order to make it unique you should to it. Then, create your unique constrain.

If you are not allowed to do this, and you want to add an unique constrain for any insert or update operation from now, you can create:

  1. stored procedure that is inserting/updating records in the table but first check for duplicates
  2. instead of insert and update trigger which is checking for duplicates and then inserting/updating the value

Check this link for more exmaples of triggers.

Not the answer you're looking for? Browse other questions tagged or ask your own question.