Check constraint - all rows in a table, for each client <= 100% (PostgreSQL)

Question

I have a table which stores a category mix for customers. The number of categories may change, but the breakdown must always be less than or equal to 100% for each customer.

custom     type_      pct  
-------    -------   -----  
Cust1      Type A    .33  
Cust1      Type B    .17  
Cust1      Type C    .50  
Cust2      Type A    .30  
Cust2      Type D    .10  
Cust2      Type E    .10  
Cust2      Type F    .50  

Any ideas on how I can add a check constraint to enforce this rule?
Here's my start...

ALTER TABLE cust_mix ADD CONSTRAINT ttl_pct_mix CHECK (SUM (pct) <= 1);  

But this checks all rows, regardless of the customer ID

Answer 1

You won't be able to achieve this just by adding a constraint. You will need a trigger after insert/update to check this on the database side.

Answer 2

Not sure if it's possible in PostgreSQL as I only use SQL Server, however, you don't actually have to do a check on a specific Customer Id in order to check your constraint. You can just check that the maximum SUM is valid. That might help in putting it into your constraint.

For example:

(
    SELECT TOP 1
        SUM(pct)
    FROM
        cust_mix
    GROUP BY
        custom
    ORDER BY
        SUM(pct) DESC
) <= 1