Postgres 고유 제한 조건과 인덱스

Postgres 고유 제한 조건과 인덱스

---

문서 를 이해할 수 있듯이 다음 정의는 동일합니다.

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

그러나 참고 사항을 읽을 수 있습니다 . 테이블에 고유 제한 조건을 추가하는 선호되는 방법은 ALTER TABLE ... ADD CONSTRAINT입니다. 고유 제한 조건을 시행하기 위해 인덱스를 사용하는 것은 직접 액세스해서는 안되는 구현 세부 사항으로 간주 될 수 있습니다 .

좋은 스타일의 문제입니까? 이러한 변형 중 하나 (예 : 성능)를 선택하면 실질적인 결과는 무엇입니까?

---

나는이 기본이지만 중요한 문제에 대해 약간의 의심이 있었으므로 모범으로 배우기로 결정했습니다.

의 테스트 테이블 만들어 보자 마스터 두 개의 열, con_id 고유 제한 조건으로하고 ind_id 고유 인덱스에 의해 색인.

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)

테이블 설명 (psql의 \ d)에서 고유 인덱스의 고유 제약 조건을 알 수 있습니다.

독창성

만약을 위해서, 독창성을 확인합시다.

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

예상대로 작동합니다!

외래 키

이제 master 의 두 열을 참조하는 두 개의 외래 키로 세부 테이블을 정의 합니다 .

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)

글쎄, 오류가 없습니다. 작동하는지 확인하십시오.

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

외래 키에서 두 열을 모두 참조 할 수 있습니다.

인덱스를 이용한 제약

기존 고유 인덱스를 사용하여 테이블 제약 조건을 추가 할 수 있습니다.

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)

이제 열 제약 조건 설명에는 차이가 없습니다.

부분 인덱스

In table constraint declaration you cannot create partial indexes. It comes directly from the definition of create table .... In unique index declaration you can set WHERE clause to create partial index. You can also create index on expression (not only on column) and define some other parameters (collation, sort order, NULLs placement).

You cannot add table constraint using partial index.

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.

---

One more advantage of using UNIQUE INDEX vs. UNIQUE CONSTRAINT is that you can easily DROP/CREATE an index CONCURRENTLY, whereas with a constraint you can't.

---

Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists.

Conceptually the index is an implementation detail and uniqueness should be associated only with constraints.

The full text

So speed performance should be same

---

Another thing I've encountered is that you can use sql expressions in unique indexes but not in constraints.

So, this does not work:

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

but following works.

CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));

---

I read this in the doc:

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

So I think it is what you call "partial uniqueness" by adding a constraint.

And, about how to ensure the uniqueness:

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

Note: The preferred way to add a unique constraint to a table is ALTER TABLE … ADD CONSTRAINT. 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.

So we should add constraint, which creates an index, to ensure uniqueness.

How I see this problem?

A "constraint" aims to gramatically ensure that this column should be unique, it establishes a law, a rule; while "index" is semantical, about "how to implement, how to achieve the uniqueness, what does unique means when it comes to implementation". So, the way Postgresql implements it, is very logical: first, you declare that a column should be unique, then, Postgresql adds the implementation of adding an unique index for you.

---

There is a difference in locking.
Adding an index does not block read access to the table.
Adding a constraint does put a table lock (so all selects are blocked) since it is added via ALTER TABLE.

참고URL : https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index