samedi 31 janvier 2015

Is unique index better than unique constraint when I need an expression index


PgSQL docs say:



The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.



Based on this, if I want an expression index on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a lower case index?


Case 1:



CREATE TABLE book (
id SERIAL PRIMARY KEY,
name text NOT NULL,
CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (lower(name));


Case 2:



CREATE TABLE book (
id SERIAL PRIMARY KEY,
name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (lower(name));




Aucun commentaire:

Enregistrer un commentaire