wake-up-neo.com

Postgres eindeutige Einschränkung gegen Index

Wie ich verstehen kann Dokumentation sind die folgenden Definitionen gleichwertig:

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

Sie können jedoch den folgenden Hinweis lesen: Die bevorzugte Methode zum Hinzufügen einer eindeutigen Einschränkung zu einer Tabelle ist ALTER TABLE ... ADD CONSTRAINT. Die Verwendung von Indizes zur Durchsetzung eindeutiger Einschränkungen kann als Implementierungsdetail betrachtet werden, auf das nicht direkt zugegriffen werden sollte .

Geht es nur um guten Stil? Welche praktischen Konsequenzen hat die Wahl einer dieser Varianten (z. B. in Bezug auf die Leistung)?

114
Adam Piotrowski

Ich hatte einige Zweifel an diesem grundlegenden, aber wichtigen Thema, also beschloss ich, an Beispielen zu lernen.

Lassen Sie uns die Testtabelle master mit zwei Spalten erstellen, con_id mit eindeutiger Einschränkung und ind_id nach eindeutigem Index.

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)

In der Tabellenbeschreibung (\ d in psql) können Sie die eindeutige Einschränkung vom eindeutigen Index unterscheiden.

Einzigartigkeit

Lassen Sie uns die Einzigartigkeit prüfen, nur für den Fall.

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

Es funktioniert wie erwartet!

Fremde Schlüssel

Jetzt definieren wir detail table mit zwei Fremdschlüsseln, die auf unsere beiden Spalten in master verweisen.

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)

Nun, keine Fehler. Stellen wir sicher, dass es funktioniert.

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

Beide Spalten können in Fremdschlüsseln referenziert werden.

Einschränkung mit Index

Sie können eine Tabelleneinschränkung mithilfe des vorhandenen eindeutigen Index hinzufügen. 

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)

Nun gibt es keinen Unterschied zwischen der Beschreibung der Spalteneinschränkungen.

Teilindizes

In der Tabelleneinschränkungsdeklaration können Sie keine Teilindizes erstellen. Es kommt direkt aus der Definition von create table .... In der eindeutigen Indexdeklaration können Sie WHERE clause festlegen, um einen Teilindex zu erstellen. Sie können auch einen Index on-Ausdruck erstellen (nicht nur für die Spalte) und einige andere Parameter definieren (Sortierung, Sortierreihenfolge, NULL-Platzierung).

Sie können keine Tabelleneinschränkung mithilfe eines partiellen Index hinzufügen.

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.
107
klin

Ein weiterer Vorteil der Verwendung von UNIQUE INDEX gegenüber UNIQUE CONSTRAINT ist, dass Sie einen Index DROP leicht CREATE/CONCURRENTLY können, während dies bei einer Einschränkung nicht möglich ist.

25
Vadim Zingertal

Einzigartigkeit ist eine Einschränkung. Es wird zufällig über die Erstellung implementiert eines eindeutigen Index, da ein Index alle vorhandenen .__ schnell durchsuchen kann. Werte, um festzustellen, ob ein bestimmter Wert bereits vorhanden ist.

Konzeptionell ist der Index ein Implementierungsdetail, und die Eindeutigkeit sollte .__ sein. nur mit Einschränkungen verbunden.

Der Volltext

Die Geschwindigkeitsleistung sollte also gleich sein

6
Eugen Konkov

Eine andere Sache, die mir begegnet ist, ist, dass Sie SQL-Ausdrücke in eindeutigen Indizes verwenden können, nicht jedoch in Einschränkungen.

Also das funktioniert nicht:

CREATE TABLE users (
    name text,
    UNIQUE (lower(name))
);

aber folgende Werke.

CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));
2
김민준

Ich habe das in der Dokumentation gelesen:

ADD table_constraint [NICHT GÜLTIG]

Dieses Formular fügt einer Tabelle eine neue Einschränkung hinzu, die dieselbe Syntax wie CREATE TABLE Und die Option NOT VALID Verwendet, die derzeit nur für Fremdschlüsseleinschränkungen zulässig ist. Wenn die Einschränkung mit NOT VALID Markiert ist, lautet die möglicherweise langwierige anfängliche Prüfung, um sicherzustellen, dass alle Zeilen in der Tabelle die Einschränkung erfüllen, übersprungen. Die Einschränkung wird weiterhin für nachfolgende Einfügungen oder Aktualisierungen erzwungen (das heißt, sie schlagen fehl, es sei denn, in der Tabelle, auf die verwiesen wird, ist eine übereinstimmende Zeile vorhanden). Die Datenbank geht jedoch erst dann davon aus, dass die Einschränkung für alle Zeilen in der Tabelle gilt , wenn sie mit der Option VALIDATE CONSTRAINT überprüft wurde.

Ich denke, es ist das, was Sie "teilweise Eindeutigkeit" nennen, indem Sie eine Einschränkung hinzufügen.

Und wie Sie die Einzigartigkeit sicherstellen können:

Durch das Hinzufügen einer eindeutigen Einschränkung wird automatisch ein eindeutiger B-Tree-Index für die in der Einschränkung aufgelisteten Spalten oder Spaltengruppen erstellt. Eine Eindeutigkeitsbeschränkung, die nur einige Zeilen abdeckt, kann nicht als eindeutige Einschränkung geschrieben werden. Eine solche Einschränkung kann jedoch durch die Erstellung eines eindeutigen Teilindex erzwungen werden.

Hinweis: Die bevorzugte Methode zum Hinzufügen einer eindeutigen Einschränkung zu einer Tabelle ist ALTER TABLE… ADD CONSTRAINT. Die Verwendung von Indizes zur Durchsetzung eindeutiger Einschränkungen kann als Implementierungsdetail betrachtet werden, auf das nicht direkt zugegriffen werden sollte. Beachten Sie jedoch, dass keine Indizes für eindeutige Spalten manuell erstellt werden müssen. dies würde nur den automatisch erstellten Index duplizieren.

Aus diesem Grund sollten wir eine Einschränkung hinzufügen, die einen Index erstellt, um die Eindeutigkeit sicherzustellen.

Wie sehe ich dieses Problem?

Eine "Einschränkung" zielt darauf ab, grammatisch sicherzustellen, dass diese Spalte eindeutig sein sollte; sie legt ein Gesetz, eine Regel fest; während "index" semantisch ist, geht es um "wie man implementiert, wie man die Eindeutigkeit erreicht, was bedeutet einzigartig, wenn es um die Implementierung geht". Die Art und Weise, wie Postgresql es implementiert, ist sehr logisch: Zuerst deklarieren Sie, dass eine Spalte eindeutig sein soll, dann fügt Postgresql die Implementierung des Hinzufügens eines eindeutigen Index für Sie hinz.

0
WesternGun

Beim Sperren gibt es einen Unterschied.
Durch das Hinzufügen eines Index wird der Lesezugriff auf die Tabelle nicht blockiert.
Durch das Hinzufügen einer Einschränkung wird eine Tabellensperre gesetzt (alle Auswahlen werden blockiert), da sie über ALTER TABLE hinzugefügt wird.

0
Bax