只要尚未提交，SQL Server 是否允许在事务中违反约束?

2022-01-06 00:00:00 sql constraints transactions sql-server

只要事务尚未提交，SQL Server 是否允许在事务中违反约束(即延迟约束)?

我有一个正在运行的未提交事务，当该事务正在运行时，我将更改我的数据以使其违反某些约束(例如具有重复的主键).当我提交事务时，数据将处于一致、有效的状态.这通常在 SQL 中允许，特别是在 MS SQL Server 中吗?

I have a running, uncommitted transaction and while this transaction is running, I will change my data so that it will violate some constraints (like having duplicate primary keys for example). When I commit the transaction, the data will be in consistent, valid state. Is this generally allowed in SQL and specifically in MS SQL Server?

推荐答案

不，抱歉.SQL Server 不允许在事务中使用延迟约束.它存在于 SQL Server 6.5 中，但在 SQL Server 2000 中被删除:

No, sorry. SQL Server does not allow deferred contraints in a transaction. It was present in SQL Server 6.5, but removed in SQL Server 2000: 

SET DISABLE_DEF_CNST_CHK ON

每个单独的语句必须是一致的等等，不管它是否在一个事务中

Each individual statement must be consistent etc, regardless of whether it is in a transaction

某些 RDBMS 确实允许这样做(例如 Oracle、Postgres、Interbase)

Some RDBMS do allow this (e.g. Oracle, Postgres, Interbase)

有一个 Microsoft Connect 请求，创建于 2006 年，要求使用此功能:

There is a Microsoft Connect request, created in 2006, asking for this feature:

在各种先有鸡还是先有蛋"的情况下，最好将参照完整性约束的检查推迟到事务的提交时间.

Option to defer foreign key constraint checking until transaction commit

There are various "chicken and egg" scenarios where it would be desirable to defer the checking of referential integrity constraints until commit time on a transaction.

允许将参照完整性约束检查推迟到事务的提交时间(作为一个选项).建议在 BEGIN TRANSACTION 上提供一个选项来指定这一点.

Allow deferring of referential integrity constraint checking until commit time on a transaction (as an option). Suggest providing an option on BEGIN TRANSACTION that specifies this.

微软的最后一次回应是在十年前:

The last response from Microsoft came a decade ago:

Sameer [MSFT] 于 2006 年 10 月 13 日下午 1:35 发表

Posted by Sameer [MSFT] on 10/13/2006 at 1:35 PM

你好，格雷格

感谢您的反馈.我们已经意识到这一点，并会在未来的版本中进行研究.

Thanks for the feedback. We are aware of this and looking into it for a future release.

Sameer Verkhedkar
SQL 引擎
[微软]

Sameer Verkhedkar
SQL Engine
[MSFT]

微软用什么来表示走开".

该功能是在 1992 年 7 月使用 SQL-92 定义的.示例语法是:

The feature was defined in July 1992 with SQL-92. An example syntax would be:

BEGIN TRANSACTION
   SET CONSTRAINTS ALL DEFERRED --applies only to the current transaction

   INSERT Customers ...
   INSERT Orders ...
   UPDATE Customers ... --add the thing we were missing

COMMIT TRANSACTION

相关文章