创建constraint时的选项:not deferrable和deferrable

本文深入探讨了SQL约束模式设置的两种主要状态:IMMEDIATE和DEFERRED,解释了它们的区别、用法以及如何通过SET CONSTRAINTS命令灵活调整约束检查的时间点,以优化数据库事务的性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

使用语法:not deferrable | deferrable [ initially { immediate | deferred } ]


not deferrable 是不可延迟,并且约束也无法更改为可延迟状态。约束会在每一句sql statement 之后都进行 check,不符合则 roll back


deferrable 是可延迟状态,在 deferrable 状态时又有2个选项,创建时缺省是initially immediate,即约束会在每一句sql statement 之后都进行 check,效果等同于not deferrable,但是可以修改延迟状态。


initially deferred 约束会在整个事务进行commit 时check,如果不符合则roll back


在deferrable状态下,可以通过set constraints修改 immediate或者deferred

Synopsis

SET CONSTRAINTS { ALL | constraint [,... ] }
                { DEFERRED | IMMEDIATE }

Parameters

ALL

The keyword indicating that the mode you are specifying should be applied to all constraints within the current transaction.

constraint

The name of a specific constraint of which you wish to set the mode.

DEFERRED

The keyword indicating that constraints (or a specific constraint) shouldn't be checked until the transaction reaches a COMMIT.

IMMEDIATE

The keyword indicating that constraints (or a specific constraint) should be checked at the end of each statement within a transaction.

Results

SET CONSTRAINTS

The message returned when a constraint mode is set successfully.

ERROR: Constraint 'constraint' does not exist

The error returned if you attempt to change the mode of a constraint that does not exist.

Description

Use the SET CONSTRAINTS command to set the constraint mode for all constraints or for a single constraint within the current transaction block. You can choose to set the constraint mode to either IMMEDIATE or DEFERRED. Use of IMMEDIATE mode will force the checking of all constraints at the end of each statement within the transaction. In DEFERRED mode, constraints are not checked until a COMMIT command is issued.

Note: PostgreSQL 7.1.x (the most current version as of the writing of this book) only supports the use of these modes with foreign key constraints, as both check and unique constraints are set to a constraint mode that is not affected by this command.

Example

The following example sets the constraint evaluation mode to IMMEDIATE for all constraints within the transaction:

SET CONSTRAINTS ALL IMMEDIATE;

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值