Aggregate Constraint

Introduction

In PostgreSQL (and standard SQL in general), Aggregate Constraint is not an officially supported syntax or keyword, but rather a concept referring to constraints based on aggregated data (Aggregate Constraint / Cross-row Constraint), which is a very common problem in data processing.
In practice, PostgreSQL directly prohibits using Aggregate Functions such as SUM, COUNT, AVG, MAX, MIN inside a table's CHECK constraint. The reason is that a CHECK constraint is evaluated on individual rows upon insertion or modification, whereas aggregate functions compute values across sets of multiple rows.
For example, you cannot use CHECK with SUM to create a constraint ensuring the total revenue percentage of categories does not exceed 100%. Postgres will throw an error regarding the use of aggregate functions within a CHECK Constraint.

Alternative Solutions

To enforce an aggregate constraint rule, you can apply the following solutions:

Using Triggers
- This is the most common approach. Using a Trigger allows executing a PL/pgSQL function on every INSERT, UPDATE or DELETE operation to calculate totals (or counts) and raise an error message if violated.
- Ensures absolute data integrity at the Database level.
- To prevent Race Condition errors when multiple transactions write data simultaneously, you should use appropriate Locking or data isolation levels within the Trigger function.
Using Materialized View + UNIQUE Constraint
- This is an advanced workaround technique applied when you want to use a Unique Constraint to catch data aggregation errors.
- A Materialized View acts by pre-computing and storing aggregate results (SUM, COUNT, etc.) as physical data (like a real table).
- Afterwards, you can create a Unique Index, Unique Constraint or EXCLUSION Constraint on that Materialized View.
- Because this approach uses Constraints, it also does not directly support Aggregate functions, but it offers good performance and simple implementation since you do not need to write custom validation logic or manage data locks.
- You can choose one of the following handling strategies:
  - Adding a Trigger/Transaction with REFRESH MATERIALIZED VIEW
    - If the View's Constraint is violated, data is ROLLBACKed immediately to avoid generating bad data.
    - However, performance is impacted because the Trigger must run after every data change.
  - Using a cronjob to run REFRESH MATERIALIZED VIEW
    - This method provides better performance, but it cannot prevent bad data from entering the Database.
    - When creating the View fails due to a Constraint, you must perform manual cleanup using corresponding UPDATE/DELETE statements.
HAVING Clause
- If your goal is not to block data writing into tables, but rather to filter data groups satisfying aggregation conditions during SELECT, use the HAVING clause.
- This approach is easy to use and suitable for reporting queries, but it cannot be used to prevent bad data from being added to the database.

Detail

Create tables as follows:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0)
);

Assuming the system has an Aggregate Constraint stating that an order cannot be created with a total product quantity exceeding 100, you can no longer create a standard Constraint. Instead, we apply the solutions above to implement it as follows:

Trigger

FOR EACH ROW

Create the Function and Trigger like this:

CREATE OR REPLACE FUNCTION check_order_item_limit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    v_total_quantity INT;
BEGIN
    PERFORM order_id 
    FROM orders 
    WHERE order_id = NEW.order_id 
    FOR UPDATE;

    SELECT COALESCE(SUM(quantity), 0) INTO v_total_quantity
    FROM order_items
    WHERE order_id = NEW.order_id;

    IF v_total_quantity > 100 THEN
        RAISE EXCEPTION 'Total quantity for order ID % exceeds the limit of 100 (Current total: %).', 
            NEW.order_id, v_total_quantity;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trg_check_order_item_limit
AFTER INSERT OR UPDATE ON order_items
DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE FUNCTION check_order_item_limit_trigger();

Using PERFORM is similar to SELECT, but it does not return data.
Calculates SUM(quantity) and stores the result in v_total_quantity, then uses it to check logic. If the requirement is violated, it executes RAISE EXCEPTION.
CREATE CONSTRAINT TRIGGER is used to check true/false data conditions (unlike standard CREATE TRIGGER, which is used to execute actions).
- Runs the check_order_item_limit_trigger function upon INSERT/UPDATE into the order_items table.
- DEFERRABLE INITIALLY IMMEDIATE determines when the Constraint Trigger will be activated within a Transaction, consisting of two parts:
  - DEFERRABLE: Allows you to actively delay the Trigger execution time to the end of the Transaction (when calling COMMIT) instead of checking immediately after each INSERT / UPDATE statement.
  - INITIALLY IMMEDIATE (Default): The trigger runs immediately after each DML statement. However, because of the DEFERRABLE attribute, you can override this behavior in code.
FOR EACH ROW handles processing individually for each row.

To test the Trigger, use a query like this:

INSERT INTO orders (order_id, customer_name) VALUES (1, 'Customer 1');

-- Statement 1
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 101, 50);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 102, 30);
COMMIT;

-- Statement 2
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 103, 30); 

-- Tx3
BEGIN;
SET CONSTRAINTS trg_check_order_item_limit DEFERRED;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 103, 30); 
COMMIT;

First, INSERT the order and run Statement 1, which will succeed because the order's product quantity has not exceeded 100. When running Statement 2, it will fail because the quantity exceeds 100, resulting in an immediate ROLLBACK where data is not INSERTed into order_items.

For Tx3, using SET CONSTRAINTS DEFERRED delays the Trigger execution. You must run COMMIT for the Trigger to be activated.

FOR EACH STATEMENT

If you find that using FOR EACH ROW impacts performance when processing each order, you can switch to using FOR EACH STATEMENT as follows:

CREATE OR REPLACE FUNCTION check_order_item_limit_stmt_lock_trigger()
RETURNS TRIGGER AS $$
DECLARE
    v_violating_order_id INT;
    v_total_quantity INT;
BEGIN
    PERFORM order_id 
    FROM orders 
    WHERE order_id IN (SELECT DISTINCT order_id FROM new_table WHERE order_id IS NOT NULL)
    ORDER BY order_id ASC
    FOR UPDATE;

    SELECT
        oi.order_id, 
        SUM(oi.quantity)
    INTO 
        v_violating_order_id, 
        v_total_quantity
    FROM order_items oi
    WHERE oi.order_id IN (SELECT DISTINCT order_id FROM new_table WHERE order_id IS NOT NULL)
    GROUP BY oi.order_id
    HAVING SUM(oi.quantity) > 100
    LIMIT 1;

    IF v_violating_order_id IS NOT NULL THEN
        RAISE EXCEPTION 'Order ID % violates aggregate constraint: Total quantity is % (exceeds the limit of 100).', 
            v_violating_order_id, v_total_quantity;
    END IF;

    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_order_item_limit_stmt_insert
AFTER INSERT ON order_items
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION check_order_item_limit_stmt_lock_trigger();

CREATE TRIGGER trg_check_order_item_limit_stmt_update
AFTER UPDATE ON order_items
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION check_order_item_limit_stmt_lock_trigger();

When creating the function check_order_item_limit_stmt_lock_trigger, I processed:
- PERFORM and FOR UPDATE to lock rows without retrieving values like SELECT does.
- It is necessary to use ORDER BY order_id ASC to lock rows in a fixed order, preventing Deadlocks when Transactions run concurrently.
- The subsequent query calculates v_violating_order_id, v_total_quantity. If SUM(oi.quantity) > 100 is violated, it executes RAISE EXCEPTION.
When using REFERENCING NEW TABLE, you cannot create a Trigger with more than 1 event, so it must be split into 2 separate Triggers for INSERT and UPDATE.

Then verify:

INSERT INTO orders (order_id, customer_name) VALUES (3, 'Customer 3');

INSERT INTO order_items (order_id, product_id, quantity) VALUES 
(3, 201, 60),
(3, 202, 50);

Materialized View + Constraint + Trigger

CREATE MATERIALIZED VIEW mv_order_violations AS
SELECT 
    order_id,
    SUM(quantity) AS total_qty,
    generate_series(1, 2) AS dup_tag
FROM order_items
GROUP BY order_id
HAVING SUM(quantity) > 100;

CREATE UNIQUE INDEX idx_prevent_order_violation ON mv_order_violations (order_id);

CREATE OR REPLACE FUNCTION refresh_order_violations_view()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mv_order_violations;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_refresh_mv_violations
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_order_violations_view();

CREATE UNIQUE INDEX idx_prevent_order_violation ensures order_id is unique in the MATERIALIZED VIEW.
CREATE MATERIALIZED VIEW uses generate_series(1, 2) to duplicate each row 2 times. Because the query retrieves orders where SUM(quantity) > 100, if any result exists, it duplicates to create 2 identical order_ids, thereby violating the idx_prevent_order_violation Constraint.
Running the refresh_order_violations_view function executes REFRESH MATERIALIZED VIEW to trigger the Unique Index check.
CREATE TRIGGER trg_refresh_mv_violations executes the refresh_order_violations_view function upon INSERT/UPDATE/DELETE on the order_items table.
FOR EACH STATEMENT is suitable for Batch Insert operations. Even if multiple rows are INSERTed, the Trigger executes only once.

Verify as follows:

INSERT INTO orders (order_id, customer_name) VALUES (2, 'Customer 2');

-- Statement 1
INSERT INTO order_items (order_id, product_id, quantity) VALUES 
(2, 201, 60),
(2, 202, 50);

-- Statement 2
INSERT INTO order_items (order_id, product_id, quantity) VALUES 
(2, 201, 60),
(2, 202, 20);

Statement 1: Blocked from creating View due to Unique Constraint violation.

Statement 2: Executed successfully.

Next, use the HAVING clause to filter grouped data. This is used when you do not create a Trigger/Constraint to validate during INSERT, requiring manual handling of bad data.

-- Query 1
SELECT 
    o.order_id,
    o.customer_name,
    SUM(oi.quantity) AS total_quantity,
    COUNT(oi.item_id) AS total_skus
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_name
HAVING SUM(oi.quantity) > 100;

-- Query 2
SELECT
    order_id,
    SUM(quantity) AS total_quantity
FROM order_items
GROUP BY order_id
HAVING SUM(quantity) <= 100;