How to use existing index to create conditional constraint-CodePudding

I have following index:

op.create_index(
    op.f("ix_order_company_id_email_lower"),
    "order",
    ["company_id", sa.text("lower(email)")],
    unique=False,
    postgresql_concurrently=True
)

Now I want to create a check constraint that is going to create sort of a 'unique' constraint for only specific type of orders.

op.execute(
    """ALTER TABLE order 
        ADD CONSTRAINT check_order_company_id_email_lower_type
        CHECK (type = 'AH01')
        NOT VALID"""
)

How can I add additional check to only apply to records in ix_order_company_id_email_lower?

EDIT: Basically this type of order can only be submitted once per email in a specific company.

CodePudding user response：

You need a partial unique index:

CREATE INDEX ON order (company_id, lower(email))
WHERE type = 'AH01';