Postgres contrainte unique vs indice de

Que je peux comprendre la documentation les définitions suivantes sont équivalentes:

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);

Cependant vous pouvez le lire dans la note de: La meilleure façon d'ajouter une contrainte unique à une table ALTER TABLE ... AJOUTER la CONTRAINTE. L'utilisation des index pour faire respecter les contraintes unique peut être considéré comme un détail d'implémentation qui ne doivent pas être accessibles directement.

Est-ce seulement une question de style? Quelles sont les conséquences pratiques de choix de l'une de ces variantes (par exemple, dans la performance)?

Le (seul) pratique, la différence est que vous pouvez créer une clé étrangère à une contrainte unique, mais pas à un index unique.
Un avantage dans l'autre sens (comme venu d'une autre question récemment), c'est que vous pouvez avoir partielle d'un index unique, comme "Unique" ( foo ) bar Où Est Nulle". Autant que je sache, il n'y a pas moyen de le faire avec une contrainte.
Je ne suis pas sûr quand c'est arrivé, mais cela ne semble plus être vrai. Ce SQL violon permet de clé étrangère des références à un index unique: sqlfiddle.com/#!17/20ee9; EDIT: ajout d'un "filtre" à l'index unique causes de cet arrêt de travail (comme prévu)
de postgres documentation: PostgreSQL crée automatiquement un index unique lorsqu'une contrainte unique ou de clé primaire est définie pour une table. postgresql.org/docs/9.4/static/indexes-unique.html
Je suis d'accord avec @user1935361, si ce n'était pas possible de créer une clé étrangère d'un index unique (avec PG 10 au moins), j'aurais rencontré ce problème il y a longtemps.
postgres liste de diffusion de réponse

InformationsquelleAutor Adam Piotrowski | 2014-05-08

113

J'ai eu quelques doutes sur cette base, mais la question importante, j'ai donc décidé d'apprendre par l'exemple.

Let's create table test maître avec deux colonnes, con_id avec contrainte unique et ind_id indexées par l'index unique.

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)

Dans la description de la table (\d dans psql) vous pouvez dire de la contrainte unique à partir de l'index unique.

Unicité

Nous allons vérifier l'unicité, juste au cas où.

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=#

Qu'il fonctionne comme prévu!

Clés étrangères

Maintenant, nous allons définir détail table avec deux clés étrangères de référencement pour nos deux colonnes dans maître.

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)

Bien, pas d'erreurs. Faisons en sorte que cela fonctionne.

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=#

Les deux colonnes peuvent être référencées dans les clés étrangères.

Contrainte à l'aide de l'indice de

Vous pouvez ajouter la contrainte de table en utilisant les index unique.

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)

Maintenant il n'y a pas de différence entre les contraintes de colonne description.

Partielle index

Dans le tableau contrainte de déclaration vous ne pouvez pas créer partielle index.
Il vient directement de la définition de create table ....
Dans l'index unique de déclaration, vous pouvez définir WHERE clause de créer des index partiel.
Vous pouvez également créer des index sur l'expression (pas seulement sur la colonne) et définir d'autres paramètres (classement, l'ordre de tri, les valeurs Null de placement).

Vous ne pouvez pas ajouter contrainte de table à l'aide partielle de l'indice.

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.

est-ce le info? en particulier sur les indices partiels
oui, il est.

InformationsquelleAutor klin

27

Un autre avantage de l'utilisation de UNIQUE INDEX vs UNIQUE CONSTRAINT est que vous pouvez facilement DROP/CREATE un index SIMULTANÉMENT, alors qu'avec une contrainte, vous ne pouvez pas.
- Autant que je sache, il n'est pas possible de déposer simultanément un index unique. postgresql.org/docs/9.3/static/sql-dropindex.html "Il y a plusieurs mises en garde d'être au courant lors de l'utilisation de cette option. Un seul indice, le nom peut être spécifié, et de la CASCADE option n'est pas prise en charge. (Ainsi, un indice qui prend en charge un seul ou de contrainte de CLÉ PRIMAIRE ne peut pas être supprimé de cette façon.)"
InformationsquelleAutor Vadim Zingertal
9

Unicité est une contrainte. Il arrive à être mis en œuvre au moyen de la création
d'un index unique puisqu'un index est rapidement en mesure de rechercher toutes les
valeurs afin de déterminer si une valeur existe déjà.

Conceptuellement l'indice est un détail de l'implémentation et de l'unicité doit être
associée uniquement à des contraintes.

Le texte intégral

Donc, la vitesse de la performance doit être la même

InformationsquelleAutor Eugen Konkov
2

Une autre chose que j'ai rencontré est que vous pouvez utiliser des expressions sql dans les index uniques, mais pas dans les contraintes.

Donc, cela ne fonctionne pas:
```
CREATE TABLE users (
    name text,
    UNIQUE (lower(name))
);
```
mais suite à des travaux.
```
CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));
```
- Je voudrais utiliser le citext extension.
InformationsquelleAutor 김민준
0

J'ai lu cela dans la doc:

AJOUTER table_constraint [ NON VALIDE ]

Cette forme ajoute une nouvelle contrainte à une table en utilisant la même syntaxe que CREATE TABLE, plus l'option NOT VALID, qui est actuellement autorisé que pour des contraintes de clés étrangères. Si la contrainte est marqué NOT VALID, potentiellement longueur initiale pour vérifier que toutes les lignes dans la table de satisfaire la contrainte est sauté. La contrainte vont encore être appliquées à l'encontre de des insertions ultérieures ou des mises à jour (qui est, ils vont pas, sauf si il ya une ligne correspondante dans la table référencée). Mais la base de données ne supposons que la contrainte est valide pour toutes les lignes de la table, jusqu'à ce qu'il est validé par l'aide de la VALIDER l'option de CONTRAINTE.

Donc je pense que c'est ce que vous appelez "partielle de l'unicité" par l'ajout d'une contrainte.

Et, sur la façon de garantir l'unicité:

L'ajout d'une contrainte unique créera automatiquement un unique index B-tree sur la colonne ou un groupe de colonnes figurant dans la contrainte. L'originalité de restriction ne couvrant que quelques lignes ne peuvent pas être écrit comme une contrainte unique, mais il est possible d'appliquer une telle restriction par la création d'un unique index partiel.

Remarque: La meilleure façon d'ajouter une contrainte unique à une table ALTER TABLE ... AJOUTER la CONTRAINTE. L'utilisation des index pour faire respecter les contraintes unique peut être considéré comme un détail d'implémentation qui ne doivent pas être accessibles directement. On doit, cependant, être conscient qu'il n'y a pas besoin de créer manuellement des index sur des colonnes uniques; le faire serait tout simplement de dupliquer automatiquement créé index.

Donc, nous devrions ajouter des contraintes, ce qui crée un index, afin d'assurer l'unicité.

Comment je vois ce problème?

Une "contrainte" vise à gramatically en sorte que cette colonne doit être unique, il établit une loi, une règle; alors que la "index" est semantical, sur "comment mettre en œuvre, comment faire pour obtenir l'unicité, ce qui ne l'unique moyen de quand il s'agit de la mise en œuvre". Ainsi, la manière de Postgresql met en œuvre, c'est très logique: tout d'abord, vous déclarez que la colonne doit être unique, alors, Postgresql ajoute la mise en œuvre de l'ajout d'un index unique pour vous.

InformationsquelleAutor WesternGun
0

Il y a une différence dans le verrouillage.

L'ajout d'un index ne bloque pas l'accès en lecture à la table.

L'ajout d'une contrainte de placer un verrou de table (tous les sélectionne sont bloqués) depuis il est ajouté via ALTER TABLE.

InformationsquelleAutor Bax

Vous devez vous connecter pour publier un commentaire.