对于这个基础但重要的问题,我有一些疑问,所以我决定通过实例来学习。
让我们创建一个测试表master,它有两列: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=#
它按照预期工作!
外键
现在我们将定义一个detail表,其中包含两个外键,分别引用我们在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子句
以创建部分索引。您还可以创建基于表达式(而不仅仅是列)的索引,并定义一些其他参数(排序规则,空值放置位置)。
您无法使用部分索引添加表约束。
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.