PostgreSQL中的可延迟检查约束

问题描述

我具有如下检查强制参与的功能：

I have function checking mandatory participation as follows:

CREATE FUNCTION member_in_has_address()
RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (SELECT *
       FROM address a, member_details b
       WHERE b.member_id = a.member_id);
END;
$$  LANGUAGE plpgsql;

然后从CHECK约束中调用

Then called from CHECK constraint

ALTER TABLE member_details
 ADD CONSTRAINT member_in_has_address_check
  CHECK (member_in_has_address());

要在Standard SQL中创建可延迟约束，应为：

To create deferable constraint in Standard SQL it would be:

ALTER TABLE member_details
 ADD CONSTRAINT member_in_has_address_check
  INITIALLY DEFERRED
  CHECK (member_in_has_address()); 

我如何在PostgreSQL中做同样的事情？

How can I do the same in PostgreSQL?

推荐答案

您可以按照与其他RDBMS相同的方式来推迟Postgresql中的约束，但是对于当前版本（9.2），您只能递减UNIQUE，PRIMARY KEY，EXCLUDE和REFERENCES。从：

You can deffer constraints in Postgresql in the same way as in other RDBMS, but for current version (9.2) you can only deffer UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES. Extract from this page of the manual:

这控制是否可以推迟
的约束。在每个命令之后，将立即检查不可延迟的约束
。可以推迟检查
可推迟的约束，直到事务结束为止（使用
SET CONSTRAINTS命令）。默认值是NOT DEFERRABLE。
当前，只有UNIQUE，PRIMARY KEY，EXCLUDE和REFERENCES（外部
键）约束接受此子句。 NOT NULL和CHECK约束
不可延迟。

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.

立即初始化

INITIALLY DEFERRED

如果约束是可延迟的，
此子句指定检查约束的默认时间。如果
约束是INITIALLY IMMEDIATE，则在每个语句之后检查它。
这是默认设置。如果约束是初始延迟的，则仅在事务结束时检查
。可以使用SET CONSTRAINTS命令更改约束检查时间
。

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

您可以从 member_details 到地址而不是当前的约束条件，以检查每个成员是否都有地址。

You can create a simple deferred foreign key from member_details to address instead of your current constraint to check, if every member has an address.

更新：您需要创建2个外键。一个常规地址，从 address（member_id）到 member_details（member_id）。另一个-从 member_details（member_id）到 address（member_id）。

UPDATE: You need to create 2 foreign key. One regular one from address(member_id) to member_details(member_id). The other one - defferred from member_details(member_id) to address(member_id).

使用这两个外键，您将能够：

With this two foreign keys you will be able to:

1. 在中创建成员member_details 。


2. 在地址中为步骤1中的成员创建地址


3. 提交（没有错误）

1. Create a member in member_details.
2. Create an address in address for member from step 1
3. Commit (with no errors)

OR

1. 在 member_details 中创建一个成员。


2. 提交（并从deferred中获取错误外键）。

1. Create a member in member_details.
2. Commit (and get error from defferred foreign key).

这篇关于PostgreSQL中的可延迟检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！