1

I have this table:

CREATE TABLE mitg.tbl_gch_customers (
    pe character varying(10)NOT NULL,
    name character varying NOT NULL,
    city character varying,
    address character varying  NOT NULL,
    state character varying,
    zip character varying ,
    country character varying,
    phone character varying,
    "primary" character(1) NOT NULL
)

How can I add a constraint to ensure that a unique row is defined as unique pe "identifier" AND "primary" = 'Y'? In other words, a "primary" customer row can only have one flagged 'Y' for each pe number. But this constraint must allow multiple "primary" = 'N' customers with same pe number.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Mike Jarvis
  • 11
  • 1
  • 1
    Do this answer your question: https://stackoverflow.com/questions/16236365/postgresql-conditionally-unique-constraint is the problem solved with ```CREATE UNIQUE INDEX unique_pe_for_primary_customer ON mitg.tbl_gch_customers (pe) WHERE ("primary"='Y');``` ? – Kristian May 10 '21 at 01:40
  • Aside: "primary" should really not be used as column name, being a [reserved word](https://www.postgresql.org/docs/current/sql-keywords-appendix.html). And the type should probably be `boolean` instead of `char(1)`. – Erwin Brandstetter May 10 '21 at 02:08

0 Answers0