Constraints and Assertions in PostgreSQL

Question

I am trying to create a simple database where I have a table of customer data and a table of order data. I am trying to write a constraint that makes it so a customer can't order more than a specific amount of items on a given day. Here's what I have:

CREATE TABLE CUSTOMER
(
    CUSTOMER_NUM CHAR(3) PRIMARY KEY,
    CUSTOMER_NAME CHAR(35) NOT NULL,
    STREET CHAR(15),
    CITY CHAR(15),
    STATE CHAR(3),
    ZIP CHAR(5),
);
CREATE TABLE ORDERS
(
    ORDER_NUM CHAR(5) PRIMARY KEY,
    ORDER_DATE DATE,
    CUSTOMER_NUM CHAR(3),

    CONSTRAINT CUSTOMER_NUM_FKEY FOREIGN KEY (CUSTOMER_NUM)
        REFRENCES CUSTOMER (CUSTOMER_NUM) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE 
);

And this is what I wrote to enforce this constraint but it does not work. I assume its because ORDER_NUM and ORDER_DATE never have equal values.

CREATE ASSERTION ITEM_LIMIT
CEHCK(
        (   SELECT COUNT(*)
            FROM CUSTOMER C1, ORDERS O1
            WHERE C1.CUSTOMER_NUM = O1.CUSTOMER_NUM AND
                O1.ORDER_DATE = O1.ORDER_NUM
     ) <= 1000

My question is how to get this constraint to work, like how to I limit the amount of orders per day.

Solution 1

As @ruakh already cleared up, there is no CREATE ASSERTION in PostgreSQL. Just check the list of SQL commands. It's not there.

You can use triggers that update a count per customer combined with a CHECK constraint, but you have to cover all relevant DML statements: INSERT, UPDATE, DELETE. Could look like this:

Prepare existing customer table:

ALTER TABLE customer ADD COLUMN order_ct integer DEFAULT 0;
UPDATE customer SET order_ct = 0;
ALTER TABLE customer ALTER order_ct SET NOT NULL;
ALTER TABLE customer ADD CONSTRAINT order_ct_max1000 CHECK (order_ct <= 1000);

Create trigger functions and triggers:

CREATE OR REPLACE FUNCTION trg_order_upaft()
  RETURNS trigger AS
$BODY$
BEGIN

IF OLD.customer_num <> NEW.customer_num THEN
    UPDATE customer
    SET    order_ct = order_ct - 1
    WHERE  customer_num = OLD.customer_num;

    UPDATE customer
    SET    order_ct = order_ct + 1
    WHERE  customer_num = NEW.customer_num;
END IF;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER upaft
  AFTER UPDATE ON orders FOR EACH ROW
  EXECUTE PROCEDURE trg_order_upaft();


CREATE OR REPLACE FUNCTION trg_order_insaft()
  RETURNS trigger AS
$BODY$
BEGIN

UPDATE customer
SET    order_ct = order_ct + 1
WHERE  customer_num = NEW.customer_num;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER insaft
  AFTER INSERT ON orders FOR EACH ROW
  EXECUTE PROCEDURE trg_order_insaft();


CREATE OR REPLACE FUNCTION trg_order_delaft()
  RETURNS trigger AS
$BODY$
BEGIN

UPDATE customer
SET    order_ct = order_ct - 1;
WHERE  customer_num = OLD.customer_num;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER delaft
  AFTER DELETE ON orders FOR EACH ROW
  EXECUTE PROCEDURE trg_order_delaft();

I made all those triggers AFTER triggers - that's why it is ok to RETURN NULL. AFTER is preferable to BEFORE in this case. It performs better if any other conditions could cancel DML statements in the middle (like other triggers).

If you have nothing of the sort, then BEFORE triggers may be preferable. Be sure to make the trigger functions RETURN NEW / OLD accordingly in this case.

Solution 2

I don't believe that PostgreSQL enforces CREATE ASSERTION statements; at least, "Assertions" is listed as an unsupported feature in Appendix D.2 of the PostgreSQL Manual. As far as I'm aware, actually, none of the major DBMSes enforces them.

The solution is to use a trigger instead; you can set it to run before any inserts on ORDERS, and to raise an error if it detects this problem. (I assume that updates on ORDERS will never introduce this problem, but if they can, then you would need a trigger for that case as well.)

Comments

[ruakh]

@Clodoaldo: That's irrelevant. It is a key word in PostgreSQL, it's just not a reserved key word; meaning that although ASSERTION is understood as a key word in a context like CREATE ASSERTION ..., it's allowed to be used as an identifier (so something like CREATE TABLE assertion ... is allowed). And PostgreSQL does accept CREATE ASSERTION statements, it just doesn't enforce the assertions that they describe.

[gestalt]

Really? Because that was how we were taught to make assertions, and we are using postgreSQL.

[ruakh]

@gestalt: Really. PostgreSQL arguably supports more of the SQL standard than any other major DBMS -- and hews closer to the standard in what it does support -- so it's logical for your class to use it. But it's not 100% of the way there. (This doesn't mean that you shouldn't learn about aspects of the standard that it doesn't support. It just means that you'll learn some things that you can't directly test.)

[onedaywhen]

Sybase SQL Anywhere supports CREATE ASSERTION and enforces them.