Difference in use Unique and Unique Constraint index in Informix?

9

I would like to know what behavior / differences between a Unique Index and Unique Constraints for Informix ?

    
asked by anonymous 21.12.2013 / 21:39

1 answer

3

Many should think that this does not make a difference in the database, but it does! And not only for the DBA / Administrator, but for developers too as it can influence how a code is written.

I work a lot with IBM Informix and know that there are differences below.

UNIQUE Index

  • Can be created / deleted ONLINE, with users using the table
    This can weigh heavily on maintaining 24x7 systems.
  • Uniqueness validation is done line by line.
    A simple example would be to execute a UPDATE tp01 SET cod = cod + 1; on a table that the cod field is sequential and with unique index, this update would give error on the 1st line since it would already duplicate
  • Like all other databases, it can not be used as a reference for Foreign keys ...

Unique Constraint

(or even primary key constraint)

  • Can not be erased / created with users accessing the table, even if only read. In other words, you need exclusive access to the table to perform maintenance. That sucks in 24x7 systems.
  • Validation is done at the end of the statement block
    In the case of UPDATE tp01 SET cod = cod + 1; , it would work without problems because it will validate the uniqueness only when finalizing the update of all the lines involved.
  • It is possible to delay the validation of the constraint only at the end of the transaction. That is, at the time of commit . This is possible when the set constraints [all|<constraint>] deffered; command is used before the updates. So in the above update example, it will only validate the uniqueness when commit is sent to the database. Note: This feature is valid for all types of constraints (PK, FK, UK)
21.12.2013 / 21:39