PostgreSQL 中的约束和断言

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

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

我的问题是如何让这个限制发挥作用，比如如何限制每天的订单量。

正如@ruakh 已经澄清的那样，没有CREATE ASSERTION在 PostgreSQL 中。只需检查SQL命令列表 http://www.postgresql.org/docs/current/interactive/sql-commands.html。它不在那里。

您可以使用触发器来更新每个客户的计数并结合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。在这种情况下，AFTER 优于 BEFORE。如果任何其他条件可以取消中间的 DML 语句（像其他触发器一样），它的性能会更好。

如果您没有这样的东西，那么 BEFORE 触发器可能会更好。在这种情况下，请务必相应地设置触发功能 RETURN NEW / OLD。