14
Apr
Tags: | 4 Comments »

Firebird – unique index vs. unique constraint

From time to time I got a question about the difference between unique key and unique constraint. Both elements enforcing uniqueness across values in column(s). But the unique index cannot be used in referential integrity constraints. The unique index can be considered as a “tool” for unique constraint.

There's 4 Comments So Far

  • kdv
    April 15th, 2008 at 16:05

    There are two constraints to make “uniqueness” of the field:Primary key and Unique constraint.While primary key is enforcing record identification, and table must have PK in 99% cases, Unique constraint is just an “alternate key”, enforcing uniqueness on the field that considered to be an alternate identifier of some entity.For example, driver license number or passport number – are just an alternate keys. They may exist, can be changed, and so on. While some person must be identified by some other identifier, mostly abstract than some real number or code.

  • kdv
    April 15th, 2008 at 16:06

    moreover. Since FB 1.5 unique constraint index allow nulls, while PK index or just unique index – not.

  • Paul Vinkenoog
    October 9th, 2009 at 00:16

    > Since FB 1.5 unique constraint index allow nulls, while PK index or just unique index – not.

    Sorry, this is not correct. A Firebird 1.5+ unique index has the same rules regarding nulls as a unique key: multiple nulls are allowed.

Who Linked To This Post?

  1. Firebird News » Firebird - unique index vs. unique constraint

Share your thoughts, leave a comment!