Difference in Indice Unique and Unique Constraint usage in Informix?

Asked

Viewed 584 times

9

I would like to know the behavior/differences between a Indice Unique and Unique Constraints to the Informix ?

sql single-restriction informix

1 answer

3


Many may think that it makes no difference in the database, but it does! And not only for the DBA/Administrator, but also for the developers because it can influence how a code is written.

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

Indice UNIQUE

  • Can be created/deleted ONLINE, with users using the table
    This can weigh heavily on the maintenance of 24x7 systems.
  • Unicity validation is done line by line.
    A simple example would be how to run a UPDATE tp01 SET cod = cod + 1; in a table that the field Cod is sequential and with Infdice unico, this update would error in the first line as it would already duplicate
  • Like all other databases, it cannot be used as a reference for Foreign Keys...

Unique Constraint

(or even Primary key Constraint)

  • Cannot be deleted/created with users accessing the table, even if read only.
    In other words, you need exclusive access to the table to perform a maintenance. That’s terrible on 24x7 systems.
  • Validation is done at the end of the statement
    In the case of UPDATE tp01 SET cod = cod + 1; , would work smoothly as it will validate the uniqueness only when finalizing the update of all lines involved.
  • It is possible to postpone the Constraint validation only at the end of the transaction.
    I mean, at the time of commit. This is possible when using the command set constraints [all|<constraint>] deffered; before the updates.
    So in the update example above, it will only validate uniqueness when the commit is sent to the bank.
    Note: This resource is valid for all types of constraints (PK, FK, UK)

Browser other questions tagged sql single-restriction informix

You are not signed in. Login or sign up in order to post.