SQL PostgreSQL 中的延迟检查约束

Deferrable check constraint in PostgreSQL

提问by Radovan Luptak

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;

Then called from CHECK constraint

然后从 CHECK 约束调用

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

To create deferable constraint in Standard SQL it would be:

要在标准 SQL 中创建可延迟约束，它将是：

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

How can I do the same in PostgreSQL?

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

回答by Igor Romanchenko

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 pageof the manual:

您可以像在其他 RDBMS 中一样延迟 Postgresql 中的约束，但对于当前版本 (9.2)，您只能延迟 UNIQUE、PRIMARY KEY、EXCLUDE 和 REFERENCES。摘自this page手册：

DEFERRABLE
NOT DEFERRABLE

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 IMMEDIATE
INITIALLY DEFERRED

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.

DEFERRABLE
NOT DEFERRABLE

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

INITIALLY IMMEDIATE
INITIALLY DEFERRED

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

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

如果每个成员都有地址，您可以创建一个简单的延迟外键 from member_detailstoaddress而不是您当前的约束来检查。

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

更新：您需要创建 2 个外键。一个常规的从address(member_id)到member_details(member_id)。另一个 - 推迟member_details(member_id)到address(member_id)。

With this two foreign keys you will be able to:

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

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

1. 中创建成员member_details。
2. address从步骤 1中为成员创建地址
3. 提交（没有错误）

OR

或者

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

1. 中创建成员member_details。
2. 提交（并从延迟的外键中获取错误）。

回答by Denis de Bernardy

Wrap your queries in a transaction, and then use a deferred foreign key and deferred constraint triggers if at least one address is needed:

将您的查询包装在一个事务中，然后在至少需要一个地址时使用延迟外键和延迟约束触发器：

CREATE CONSTRAINT TRIGGER member_details_address_check_ins
  AFTER INSERT ON member_details
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE member_details_address_check_ins();

ALTER TABLE address
ADD CONSTRAINT address_member_details_member_id_fkey
FOREIGN KEY (member_id) REFERENCES member_details(member_id)
ON UPDATE NO ACTION ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED;

CREATE CONSTRAINT TRIGGER address_member_details_check_del
  AFTER DELETE ON address
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE address_member_details_check_del();

-- also consider the update cases for the inevitable merge of duplicate members.

On a separate note, normalized and pretty, but putting addresses and contact details such as emails in a separate address table occasionally introduces very colorful UI/UX issues. E.g. an untrained secretary changing the company and address of all of her boss' contacts at company A when one of them switched to company B. Yeah, seen it happen for real when the UI behaved differently from Outlook...

在单独的注释中，规范化和漂亮，但将地址和联系方式（例如电子邮件）放在单独的地址表中偶尔会引入非常丰富多彩的 UI/UX 问题。例如，一位未经培训的秘书更改了她在 A 公司的所有老板的联系人的公司和地址，其中一个人切换到了 B 公司。是的，当 UI 的行为与 Outlook 不同时，它确实发生了……

Anyway, and fwiw, I've found that it's usually more convenient to store this stuff in the same table as the contact, i.e. address1, address2, email1, email2, etc. It makes other things simpler for a variety of other reasons -- namely running checks like the one you're looking into. The extremely rare case where you'd want to store more than two such pieces of information are, in practice, simply not worth the hassle.

无论如何，顺便说一句，我发现将这些东西与联系人存储在同一个表中通常更方便，即地址 1、地址 2、电子邮件 1、电子邮件 2 等。由于各种其他原因，它使其他事情变得更简单——即像您正在调查的那样运行检查。您想要存储两条以上这样的信息的极为罕见的情况在实践中根本不值得麻烦。

回答by Radovan Luptak

This is what I come up with.

这就是我想出的。

ALTER TABLE address
ADD CONSTRAINT address_member_in_has_address
FOREIGN KEY (member_id) REFERENCES member_details(member_id)
ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED;

CREATE FUNCTION member_in_has_address() RETURNS trigger AS $BODY$
    BEGIN
    IF NOT EXISTS(SELECT * 
                   FROM member_details
                   WHERE member_id IN (SELECT member_id 
                                        FROM address)) 
    THEN
            RAISE EXCEPTION 'Error: member does not have address';
        END IF;
    RETURN NEW;
    END;
$BODY$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER manatory_participation_member_details_ins
 AFTER INSERT ON member_details 
 DEFERRABLE INITIALLY DEFERRED 
 FOR EACH ROW  
 EXECUTE PROCEDURE member_in_has_address();

CREATE CONSTRAINT TRIGGER manatory_participation_member_details_del
 AFTER INSERT ON member_details 
 DEFERRABLE INITIALLY DEFERRED 
 FOR EACH ROW 
 EXECUTE PROCEDURE member_in_has_address();

I tried Igor's version using foreign keys in both tables without the triggers. In this case this constraint is not deffered.

我在没有触发器的两个表中使用外键尝试了 Igor 的版本。在这种情况下，不会推迟此约束。

ALTER TABLE member_details
ADD CONSTRAINT member_details_in_has_address
FOREIGN KEY (address_id) REFERENCES address
ON UPDATE NO ACTION ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED;

I get this: ERROR: null value in column "address_id" violates not-null constraint

我得到这个：错误：“address_id”列中的空值违反了非空约束

When inserting using this annonymous block:

使用此匿名块插入时：

DO $$ 
DECLARE 
 mem BIGINT;
BEGIN
INSERT INTO member_details (member_first_name, member_last_name, member_dob, member_phone_no, 
member_email, member_gender, industry_position, account_type, music_interests)
VALUES ('Rado','Luptak','07/09/80','07540962233','[email protected]','M','DJ','basic','hard core');

SELECT member_id 
 INTO mem
FROM member_details
WHERE member_first_name = 'Rado' AND member_last_name = 'Luptak'
AND member_dob = '07/09/76';

INSERT INTO address (address_id, house_name_no, post_code, street_name, town, country, member_id)
VALUES (mem, '243', 'E17 3TT','Wood Road','London', 'UK', mem);

UPDATE member_details
 SET  address_id = mem WHERE member_id = mem;
END
$$;

Another problem with enforcing mandatory participation in member_details using address_id of address table (Igor's version) is that this allows me to insert row into member_details and reference an existing address row, but the existing address row references different member_details row. When the latter member_details row is deleted it cascades and deletes the address row, which can or cannot delete (depends on settings) the new inserted member_details row. It would also return different details when joining on member_id and on address_id. Therefore, it requires another constraint, so I stayed with trigger and dropping it before insert and recreating it after insert, due to the trigger is not deferred.

使用地址表（Igor 的版本）的 address_id 强制参与 member_details 的另一个问题是，这允许我将行插入 member_details 并引用现有地址行，但现有地址行引用不同的 member_details 行。当后面的 member_details 行被删除时，它会级联并删除地址行，地址行可以或不能删除（取决于设置）新插入的 member_details 行。在加入 member_id 和 address_id 时，它还会返回不同的详细信息。因此，它需要另一个约束，所以我一直使用触发器并在插入之前删除它并在插入之后重新创建它，因为触发器没有延迟。