我有下表
CREATE TABLE "prm_project_service_product_and_services" (
"id" BIGSERIAL NOT NULL,
"name" VARCHAR(60) NOT NULL,
"note" VARCHAR(256) NOT NULL,
"version" BIGINT DEFAULT NULL,
"created_date" TIMESTAMP DEFAULT NULL,
"created_by_id" BIGINT DEFAULT NULL,
"last_modified_date" TIMESTAMP DEFAULT NULL,
"last_modified_by_id" BIGINT DEFAULT NULL,
"deleted" BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY ("id"),
CONSTRAINT project_service_product_and_services_unique UNIQUE ("name")
);
我想只在deleted
false
时name
独一无二,这可能吗?
您可以使用部分唯一索引:
create unique index punq_prm_project_service_product_and_services
on prm_project_service_product_and_services(name)
where not deleted;
如文档中所述,这必须使用索引而不是唯一约束来完成:
仅涵盖某些行的唯一性限制不能写为唯一约束,但可以通过创建唯一分部索引来强制实施此类限制。