PostgreSQL中的约束和断言

Question

我正在尝试创建一个简单的数据库，其中有一张客户数据表和一张订单数据表。我正在尝试写一个约束，使客户在给定的一天不能订购超过特定数量的商品。这是我所拥有的：

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 
);

这就是我为强制执行此约束而写的内容，但它不起作用。我认为这是因为ORDER_NUM和ORDER_DATE永远不会有相等的值。

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

我的问题是如何使这种约束发挥作用，例如如何限制每天的订单量。

Answer 1

由于@ruakh已经清除，因此CREATE ASSERTIONPostgreSQL中没有。只需检查SQL命令列表即可。不在那里

您可以使用触发器来结合CHECK约束来更新每个客户的计数，但是您必须涵盖所有相关的DML语句：INSERT，UPDATE，DELETE。可能看起来像这样：

准备现有的客户表：

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 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();

我在触发器之后做了所有这些触发器-这就是为什么可以的原因RETURN NULL。在这种情况下，“后”比“前”更可取。如果其他任何条件都可以取消中间的DML语句（如其他触发器），则效果会更好。

如果您一无所获，则最好使用BEFORE触发器。在这种情况下，请确保使触发功能相应地返回NEW / OLD。