Difference in using Unique index and Unique Constraint in Sql Server?
I would like to know what the behavior/differences between a unique index and Unique Constraints for the Sql Server?
Many should think that this makes no difference to the database, but it does! And not only for the DBA/administrator, but for the developers as well because it can influence how a code is written.
As a reference of what can be so different and that I am interested to Know follows below an example of how is it in a bank Informix , but in SQL Server how does it work?
Single index
- can be created / deleted ONLINE, with users using the table
This can weigh heavily on the maintenance of 24x7 systems. * Validation from uniqueness is made line by line. A simple example would be like run aUPDATE tp01 SET cod = cod + 1;in a table that the field cod is sequential and with a single index, this update would give error in the 1st line because it would already duplicate * Like all other databases, no it can be used as a foreign keys reference...Unique Constraint
(or even primary key constraint)
- cannot be deleted / created with users accessing the table, even that just reading. In other words, exclusive access is required in the table to perform a maintenance. This is lousy on systems 24x7. * Validation is done at the end of the statement No case of
UPDATE tp01 SET cod = cod + 1;, it would work without problems because it will validate the uniqueness only when you finish updating all lines involved. * It is possible to postpone the validation of constraint only at the end of the transaction. That is, at the time ofcommit. This is possible when theset constraints [all|<constraint>] deffered;Command is used before updates.
So in the example of the update above, it will only validate the uniqueness when commit is sent to the bank. Obs.: This feature applies to all types of constraints (PK, FK, UK)
Source: difference in using Unique index and Unique Constraint in Informix?
Remembering, the description above is how IBM Informix works. I would like to know how it is in the other banks and understand what limitations or additional features we can provide for developers.
2 answers
In terms of performance and assertiveness the two are practically equivalent:
A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. there are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating a UNIQUE constraint on the column makes the objective of the index clear.
Aside from the caveat itself, the rest of the MSDN documentation does little to differentiate the two ways.
Despite the similarity highlighted by the documentation on MSDN, one of our sister sites has an answer that demonstrates some small differences :
- violation of a unique constraint returns an error 2627, while of a unique index returns 2601.
- a unique constraint cannot be disabled, while a unique index is possible.
-
unique constraints support
IGNORE_DUP_KEYandFILLFACTOR(may be SQL version dependent). - unique constraint cannot be filtered (?)
The unique key guarantees the uniqueness of information in your table, the primary key also but the latter can be used for foreign key relationships with other tables.
Usually unique keys can also have null records, so they cannot be primary key. One should be attentive to this.