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

我的问题是如何使此约束条件得以实现，比如我如何限制每天的订单数量。

- gestalt

---

断言（Assertion）不是PostgreSQL的保留关键字，但它是SQL-99和92的保留关键字。 - Clodoaldo Neto

@Clodoaldo：那不相关。在PostgreSQL中，它是一个关键字，只是不是保留关键字；这意味着尽管ASSERTION在像CREATE ASSERTION ...这样的上下文中被理解为关键字，但允许将其用作标识符（因此允许使用类似CREATE TABLE assertion ...的语句）。而且，PostgreSQL确实接受CREATE ASSERTION语句，只是不强制执行它们描述的约束条件。 - ruakh

如果你尝试在PostgreSQL中创建一个断言（至少在9.4版本之前），你会得到“ERROR: CREATE ASSERTION is not yet implemented”的错误。所以我认为它不接受它们。你描述的行为（接受但不强制执行）更像是MySQL。 - beldaz

@beldaz：嗯，当我发表那条评论时，我有一个可用的PostgreSQL实例；所以我想我测试过了……但是现在我不记得我是否这样做了。如果我写错了，那么谢谢你的纠正。 :-) - ruakh

Answer 1

正如@ruakh已经澄清的那样，PostgreSQL中没有CREATE ASSERTION。只需查看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();

我将所有触发器都设置为AFTER触发器，所以使用RETURN NULL是可以的。在这种情况下，AFTER优于BEFORE。如果有任何其他条件可能在中途取消DML语句（比如其他触发器），它会表现得更好。

如果您没有类似的情况，那么BEFORE触发器可能更可取。确保在这种情况下使触发器函数返回NEW/OLD。

- Erwin Brandstetter

Answer 2

我不相信PostgreSQL会强制执行CREATE ASSERTION语句；至少在PostgreSQL手册的附录D.2中，“Assertions”被列为不受支持的功能。据我所知，实际上，所有主要的DBMS都没有强制执行它们。 解决方案是使用触发器；您可以将其设置为在对ORDERS进行任何插入之前运行，并在检测到此问题时引发错误。（我假设ORDERS上的更新永远不会引入此问题，但如果它们可以，那么您也需要为该情况编写一个触发器。）

- ruakh

---

真的吗？因为这就是我们被教导如何进行断言的方式，而且我们正在使用PostgreSQL。 - gestalt

@gestalt: 的确。 PostgreSQL可以说是支持SQL标准比其他主要DBMS更多的数据库管理系统 - 并且在其支持的内容上更贴近标准 - 所以使用它是很合理的。 但它并不是百分之百符合标准。（这并不意味着您不应该学习它不支持的标准方面。这只是意味着您会学到一些无法直接测试的东西。） - ruakh

Sybase SQL Anywhereو”¯وŒپCREATE ASSERTIONه¹¶ه¼؛هˆ¶و‰§è،Œه®ƒن»¬م€‚ - onedaywhen