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

Question

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

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

Answer 1

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

SET DISABLE_DEF_CNST_CHK ON

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

（例如 Oracle、Postgres、Interbase

一些 RDBMS 确实允许这样做

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

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

存在各种“先有鸡还是先有蛋”的场景，需要将参照完整性约束的检查推迟到事务的提交时间。

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

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

发布者：Sameer [MSFT]，2006 年 10 月 13 日下午 1:35

你好格雷格，

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

萨米尔·维尔赫德卡
SQL引擎
[微软金融时报]

这是微软对“走开”的说法。

SQL-92 定义了

该功能于 1992 年 7 月通过 SQL-92 定义。 。语法示例如下：

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

Answer 2

您可以在运行事务时禁用约束，然后在完成后重新启用它们。

ALTER TABLE mytable NOCHECK CONSTRAINT myconstraint

--... RUN TRANSACTION

ALTER TABLE mytable WITH CHECK CHECK CONTRAINT ALL

警告：这将影响所有连接。

Answer 3

如果必须（例如从导入文件中清理数据的过程），则将中间数据放入临时表或表变量或临时表中，然后仅在清理后对带有约束的真实表执行操作，并且使数据正确。

Answer 4

SQL Server 没有延迟约束选项。但在某些情况下，您可以使用支持忽略约束的批量插入，而不会出现禁用问题。有关详细信息，您可以查看以下链接：

• 通过批量导入操作控制约束检查

• 批量插入< /a> 和 插入：使用 OPENROWSET和 BULK 批量加载数据

然后在提交事务之前，您必须手动检查整个表的约束。

Answer 5

您也可以使用合并命令。它们一起解决，绕过约束中间状态。这是一个例子。在插入之前，John 的 pk id=1，Marta 的 pk id=2。名字和姓氏是唯一的。合并命令开关放置在 MERGE 之后，John 的 PK id=2 且 Marta id=1。使用普通更新命令无法切换 Marta 和 Joe 名称。

CREATE TABLE dbo.People
(
    Id        int          NOT NULL IDENTITY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName  NVARCHAR(50) NOT NULL,
    CONSTRAINT UQ_People UNIQUE (FirstName, LastName)
);

--SELECT * FROM People;
--TRUNCATE TABLE People;

INSERT INTO People (FirstName, LastName)
VALUES
    (N'John', N'Doe'),
    (N'Marta', N'Smith'),
    (N'Julius','Manfred');

MERGE dbo.People AS P
USING (
      SELECT 1 AS Id, 'Marta' AS FirstName, 'Smith' AS LastName
       UNION
      SELECT 2, 'John', 'Doe'
       UNION
      SELECT 8, 'Martin', 'Berg') AS Flip
ON P.Id = Flip.Id
WHEN MATCHED AND (P.FirstName <> Flip.FirstName OR P.LastName <> Flip.LastName) THEN
    UPDATE
       SET P.FirstName = Flip.FirstName,
           P.LastName  = Flip.LastName
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (FirstName,
     LastName)
    VALUES
        (Flip.FirstName,
         Flip.LastName)
WHEN NOT MATCHED BY SOURCE THEN DELETE
    OUTPUT INSERTED.*, DELETED.*, $action;

SELECT *
  FROM People;