[sql] Postgres 고유 제한 조건과 인덱스

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

의 테스트 테이블 만들어 보자 마스터 두 개의 열, 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)

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

부분 인덱스

테이블 제약 조건 선언에서는 부분 인덱스를 만들 수 없습니다. 그것은에서 직접 제공 정의 의 create table .... 고유 인덱스 선언에서 WHERE clause부분 인덱스를 생성하도록 설정할 수 있습니다. 또한 표현식 (열뿐만 아니라)에 대한 색인 을 작성 하고 다른 매개 변수 (데이터 정렬, 정렬 순서, NULL 배치)를 정의 할 수도 있습니다 .

당신은 할 수없는 부분 인덱스를 사용하여 테이블에 제약 조건을 추가 할 수 있습니다.

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.

답변

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

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