sql - multiple - postgres create unique

Restricción única de Postgres frente a índice

Question

Como puedo entender la documentation las siguientes definiciones son equivalentes:

create table foo (
    id serial primary key,
    code integer,
    label text,
    constraint foo_uq unique (code, label));

create table foo (
    id serial primary key,
    code integer,
    label text);
create unique index foo_idx on foo using btree (code, label);    

Sin embargo, puede leer en la nota: La forma preferida de agregar una restricción única a una tabla es ALTERAR TABLA ... AÑADIR RESTRICCIÓN. El uso de índices para imponer restricciones únicas podría considerarse un detalle de implementación al que no se debería acceder directamente .

¿Es solo una cuestión de buen estilo? ¿Cuáles son las consecuencias prácticas de elegir una de estas variantes (por ejemplo, en el rendimiento)?

Answer 1

La unicidad es una restricción. Sucede que se implementa mediante la creación de un índice único, ya que un índice puede buscar rápidamente todos los valores existentes para determinar si ya existe un valor determinado.

Conceptualmente, el índice es un detalle de implementación y la exclusividad debe asociarse solo con restricciones.

El texto completo

Entonces el rendimiento de la velocidad debe ser el mismo

Answer 2

Tenía algunas dudas sobre este tema básico pero importante, así que decidí aprender con el ejemplo.

Creemos un maestro de tabla de prueba con dos columnas, con_id con restricción única e ind_id indexadas por índice único.

create table master (
    con_id integer unique,
    ind_id integer
);
create unique index master_unique_idx on master (ind_id);

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_unique_idx" UNIQUE, btree (ind_id)

En la descripción de la tabla (/ d en psql) puede indicar una restricción única a partir de un índice único.

Unicidad

Comprobemos la singularidad, por las dudas.

test=# insert into master values (0, 0);
INSERT 0 1
test=# insert into master values (0, 1);
ERROR:  duplicate key value violates unique constraint "master_con_id_key"
DETAIL:  Key (con_id)=(0) already exists.
test=# insert into master values (1, 0);
ERROR:  duplicate key value violates unique constraint "master_unique_idx"
DETAIL:  Key (ind_id)=(0) already exists.
test=#

¡Funciona como se esperaba!

Llaves extranjeras

Ahora definiremos la tabla de detalle con dos claves externas que hacen referencia a nuestras dos columnas en el maestro .

create table detail (
    con_id integer,
    ind_id integer,
    constraint detail_fk1 foreign key (con_id) references master(con_id),
    constraint detail_fk2 foreign key (ind_id) references master(ind_id)
);

    Table "public.detail"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Foreign-key constraints:
    "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

Bueno, no hay errores. Asegurémonos de que funcione.

test=# insert into detail values (0, 0);
INSERT 0 1
test=# insert into detail values (1, 0);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
DETAIL:  Key (con_id)=(1) is not present in table "master".
test=# insert into detail values (0, 1);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
DETAIL:  Key (ind_id)=(1) is not present in table "master".
test=#

Ambas columnas se pueden referenciar en claves externas.

Restricción usando el índice

Puede agregar una restricción de tabla usando un índice único existente.

alter table master add constraint master_ind_id_key unique using index master_unique_idx;

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
Referenced by:
    TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

Ahora no hay diferencia entre la descripción de las restricciones de la columna.

Índices parciales

En la declaración de restricción de tabla, no puede crear índices parciales. Viene directamente de la definition de create table ... En la declaración de índice único, puede establecer la WHERE clause para crear un índice parcial. También puede crear un índice en la expresión (no solo en la columna) y definir algunos otros parámetros (intercalación, orden de clasificación, ubicación NULLs).

No puede agregar restricción de tabla usando índice parcial.

alter table master add column part_id integer;
create unique index master_partial_idx on master (part_id) where part_id is not null;

alter table master add constraint master_part_id_key unique using index master_partial_idx;
ERROR:  "master_partial_idx" is a partial index
LINE 1: alter table master add constraint master_part_id_key unique ...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

Answer 3

Una ventaja más del uso de UNIQUE INDEX frente a UNIQUE CONSTRAINT es que puede DROP / CREATE un índice CONCURRENTLY , mientras que con una restricción no puede hacerlo.