What's the difference (if any) between adding an index with a "unique" contraint (indexes->create index) and adding a unique key (constraints->add unique key) in phppgadmin?

    5 days later

    bump (maybe Sxooter knows 🙂 )

      Ya got me. If you do it each way and do a \d tablename what does it show as a difference?

      I'd expect them to be functionally the same.

        Ya, \d lists both types as the same under indexes. However, in the dump, the unique indexes are created using:

        CREATE UNIQUE INDEX key_table1_field1 ON table1 USING btree (field1);

        While the unique keys are created with:

        ALTER TABLE ONLY table1
    ADD CONSTRAINT key_table1_field1 UNIQUE (field1);

        They both appear to do the same thing, but is there any reason for using one over the other?

          I can't think of why one would be preferred to the other, except maybe portability.

            Hi,

            Since I wrote phpPgAdmin, perhaps I can best answer that 🙂

            CREATE UNIQUE INDEX form is a postgres-only extension for creating unique indexes. Before say PostgreSQL 7.2 or so, there was no way of creating a unique constraint. A unique constraint is simply a unique, btree, non-partial, non-expressional index.

            Basically, the reason why the phppgadmin create index screen lets you create unique index is so you can create unique rtree indexes, unique partial indexes, etc.

            There really is no difference between a unique btree index and a unique constraint - so don't stress too much about it.

            Chris

              Write a Reply...