SQL Server是否允许事务中的约束违反，只要它还没有提交？ [英] Does SQL Server allow constraint violations in a transaction as long as it's not committed yet?

问题描述

只要事务尚未提交，SQL Server是否允许事务中的约束违规（即延迟约束）？

我有一个运行，未提交的事务，并且当此事务正在运行时，我将更改我的数据，以便它将违反一些约束（例如具有重复的主键）。当我提交事务时，数据将处于一致，有效的状态。这是通常允许在SQL和特别是在MS SQL Server？

解决方案

不，对不起。 SQL Server不允许事务中的延迟约束。它出现在SQL Server 6.5中，但在SQL Server 2000中已删除：

  SET DISABLE_DEF_CNST_CHK ON 
  

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

有些RDBMS允许这样做（例如Oracle，Postgres，Interbase）

连接

a href =https://connect.microsoft.com/SQLServer/feedback/details/124728/option-to-defer-foreign-key-constraint-checking-until-transaction-commit =nofollow> Microsoft Connect请求，在2006年创建，要求此功能：

选项推迟外键约束检查，直到事务提交

有各种鸡和鸡蛋场景，希望延迟参照完整性约束的检查，直到事务上的提交时间。 p>

允许推迟引用完整性约束检查，直到事务上的提交时间（作为选项）。建议在BEGIN TRANSACTION上提供指定此选项。

Microsoft十年前的最后一个回应是：

在10/13/2006下午1:35发表于Sameer [MSFT]

Hello Greg ，

感谢您的反馈。

Sameer Verkhedkar

SQL引擎

[MSFT]

这是Microsoft为离开发言。

SQL-92定义它

此功能在 July 1992 with SQL-92 。示例语法为：

  BEGIN TRANSACTION 
 SET约束ALL DEFERRED  - 仅适用于当前事务
 
 INSERT客户... 
 INSERT订单... 
 UPDATE客户...  - 添加我们缺少的东西
 
 COMMIT TRANSACTION 
  

Does SQL Server allow constraint violations (i.e. deferred constraints) in a transaction as long as the transaction has not been committed yet?

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?

解决方案

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

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

Connect

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.

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:

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

Hello Greg,

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

Sameer Verkhedkar
SQL Engine
[MSFT]

Which is Microsoft speak for "go away".

SQL-92 defines it

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

这篇关于SQL Server是否允许事务中的约束违反，只要它还没有提交？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！