4

I have the following table 

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

I want to make name unique only when deleted is false, is this possible ?

Dimitri Kopriwa
  • 13,139
  • 27
  • 98
  • 204
  • [Possible duplicate](https://stackoverflow.com/questions/16236365/postgresql-conditionally-unique-constraint). On an unrelated note, how would that work if deleted is turned back to true? – Ferox Feb 02 '19 at 16:24

1 Answers1

5

You can use a partial unique index:

create unique index punq_prm_project_service_product_and_services
    on prm_project_service_product_and_services(name)
    where not deleted;

As explained in the documentation, this must be done with an index and not a unique 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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786