Conditional UNIQUE constraint on table?

1.2k Views Asked by At

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.

0

There are 0 best solutions below