PostgreSQL中的约束和断言 [英] Constraints and Assertions in PostgreSQL

问题描述

我试图创建一个简单的数据库，其中有一个客户数据表和一个订单数据表。我试图写一个约束，使得客户不能在给定日期订购超过特定数量的项目。这是我有的：

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

这是我写的是强制执行这个约束，但它不工作。我假设它是因为ORDER_NUM和ORDER_DATE从不具有相等的值。

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.

推荐答案

由于@ruakh已清除，因此没有 CREATE ASSERTION 在PostgreSQL中，只需检查 SQL命令列表，它不是

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

您可以使用触发器更新每个客户的计数以及 CHECK 约束，但您必须覆盖所有相关的DML语句：INSERT，UPDATE，DELETE。可能如下所示：

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:

准备现有客户表：

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

我所有的触发器都是AFTER触发器 - 这就是为什么 RETURN NULL 。在这种情况下，AFTER优于BEFORE。如果任何其他条件可以取消中间的DML语句（如其他触发器），它执行得更好。

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

如果没有任何排序，那么BEFORE触发器可能更好。在这种情况下，请务必使触发功能RETURN NEW / OLD。

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.

这篇关于PostgreSQL中的约束和断言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！