PostgreSQL 中的约束和断言
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9657048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Constraints and Assertions in PostgreSQL
提问by gestalt
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.
这就是我为强制执行此约束而编写的内容，但它不起作用。我认为这是因为 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
My question is how to get this constraint to work, like how to I limit the amount of orders per day.
我的问题是如何让这个约束起作用，比如如何限制每天的订单量。
回答by Erwin Brandstetter
As @ruakh already cleared up, there is no CREATE ASSERTIONin PostgreSQL. Just check the list of SQL commands. It's not there.
由于@ruakh 已经清除，CREATE ASSERTIONPostgreSQL 中没有。只需检查SQL 命令列表。它不在那里。
You can use triggers that update a count per customer combined with a CHECKconstraint, but you have to cover all relevant DML statements: INSERT, UPDATE, DELETE. Could look like this:
您可以使用结合CHECK约束更新每个客户计数的触发器，但您必须涵盖所有相关的 DML 语句：INSERT、UPDATE、DELETE。看起来像这样：
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).
我在触发器之后制作了所有这些触发器 - 这就是为什么RETURN NULL. 在这种情况下，AFTER 优于 BEFORE。如果任何其他条件可以在中间取消 DML 语句（如其他触发器），它的性能会更好。
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.
如果您什么都没有，那么 BEFORE 触发器可能更可取。在这种情况下，请确保相应地使触发器功能 RETURN NEW / OLD。
回答by ruakh
I don't believe that PostgreSQL enforces CREATE ASSERTIONstatements; at least, "Assertions" is listed as an unsupported feature in Appendix D.2 of the PostgreSQL Manual. As far as I'm aware, actually, noneof the major DBMSes enforces them.
我不相信 PostgreSQL 会强制执行CREATE ASSERTION语句；至少，“断言”在 PostgreSQL 手册的附录 D.2 中被列为不受支持的功能。据我所知，实际上，没有一个主要的 DBMS 强制执行它们。
The solution is to use a triggerinstead; you can set it to run before any inserts on ORDERS, and to raise an error if it detects this problem. (I assume that updateson ORDERSwill never introduce this problem, but if they can, then you would need a trigger for that case as well.)
解决方案是改用触发器；您可以将其设置为在任何插入之前运行ORDERS，并在检测到此问题时引发错误。（我假设的更新上ORDERS绝不会介绍这个问题，但如果他们能，那么你就需要为这种情况下，也触发。）
