oracle SQL Server 中的可延迟约束

Deferrable Constraints in SQL Server

提问by LBushkin

Do any versions of SQL Server support deferrable constraints (DC)?

是否有任何版本的 SQL Server 支持可延迟约束 (DC)？

Since about version 8.0, Oracle has supported deferrable constraints- constraints that are only evaluated when you commit a statement group, not when you insert or update individual tables. Deferrable constraints differ from just disabling/enabling constraints, in that the constraints are still active - they are just evaluated later (when the batch is committed).

从大约 8.0 版开始，Oracle 就支持可延迟约束- 仅在您提交语句组时评估的约束，而不是在您插入或更新单个表时。可延迟约束与仅禁用/启用约束不同，因为约束仍然处于活动状态——它们只是稍后（当批处理被提交时）被评估。

The benefit of DC is that they allow updates that individually would be illegal to be evaluated that cummulatively result in a valid end state. An example is creating circular references in a table between two rows where each row requires a value to exist. No individual insert statement would pass the constraint - but the group can.

DC 的好处在于，它们允许对单独非法的更新进行评估，这些更新累积地导致有效的最终状态。一个例子是在两行之间的表中创建循环引用，其中每行都需要一个值。没有单独的插入语句会通过约束 - 但组可以。

To clarify my goal, I am looking to port an ORM implementation in C# to SQLServer - unfortunately the implementation relies on Oracle DC to avoid computing insert/update/delete orders amongst rows.

为了阐明我的目标，我希望将 C# 中的 ORM 实现移植到 SQLServer - 不幸的是，该实现依赖于 Oracle DC 来避免在行之间计算插入/更新/删除订单。

采纳答案by A-K

So far SQL Server does not support them. What is the problem you are solving?

到目前为止，SQL Server 不支持它们。你正在解决的问题是什么？

回答by Mirko Klemm

OT: There are IMHO quite a few things SQL Server does not support, but would make sense in an enterprise environment:

OT：恕我直言，SQL Server 不支持很多东西，但在企业环境中是有意义的：

• Deferrable constraints as mentioned here
• MARS: Just why do you need to set an option for something entirely natural?
• CASCADE DELETE constraints: SQL Server does only allow one single cascadation path for a given CASCADE DELETE constraint. Again, I don't see a reason why it shouldn't be allowed to cascade on deletion through multiple possible paths: In the end, at the time it really is executed, there will always be only one path being actually used, so why is this restriction?
• Prevention of parallel transactions on a single ADO.NET connection.
• Forcing of every command executed on a connection that has a transaction to be executed within this transaction.
• When creating a UNIQUE index, NULL is treated as if it was an actual value, and allowed to appear only once in the index. SQL's notion of NULL as an "unknown value" would, however, indicate, that NULL values be ignored altogether when creating the index...

• 此处提到的可延迟约束
• MARS：为什么你需要为完全自然的东西设置一个选项？
• CASCADE DELETE 约束：对于给定的 CASCADE DELETE 约束，SQL Server 只允许一个级联路径。同样，我没有看到为什么不允许通过多个可能的路径级联删除的原因：最后，在它真正执行的时候，总是只有一个路径被实际使用，所以为什么这是限制吗？
• 防止单个 ADO.NET 连接上的并行事务。
• 强制在具有要在此事务中执行的事务的连接上执行的每个命令。
• 创建 UNIQUE 索引时，NULL 被视为实际值，并且只允许在索引中出现一次。SQL 将 NULL 作为“未知值”的概念将表明，在创建索引时完全忽略 NULL 值......

All these little things make many of the referential integrity and transactional features you would expect from a full-sized RDBMS nearly useless in SQL Server. For example, since deferrable constraints are not supported, the notion of a "transaction" as an externally consistent Unit Of Work is partly negated, the only viable solution - except fro some dirty workarounds - being to not define referential integrity constraints at all. I would expect, the natural behavior of a transaction be that you can work inside it in the way and order of operations you like, and the system will make sure it is consistent at the time you commit it. Similar problems arise from the restriction, that a referential integrity constraint with ON DELETE CASCADE may only be defined in a way that only one single constraint can lead to the cascaded deletion of an object. This really doesn't fit most real-world scenarios.

所有这些小事情使您期望从全尺寸 RDBMS 获得的许多参照完整性和事务功能在 SQL Server 中几乎毫无用处。例如，由于不支持可延迟约束，“事务”作为外部一致工作单元的概念被部分否定，唯一可行的解​​决方案 - 除了一些肮脏的解决方法 - 根本不定义参照完整性约束。我希望，事务的自然行为是您可以按照您喜欢的操作方式和顺序在其中工作，并且系统将确保它在您提交时保持一致。类似的问题来自限制，具有 ON DELETE CASCADE 的参照完整性约束只能以一种方式定义，即只有一个约束才能导致对象的级联删除。这真的不适合大多数现实世界的场景。

回答by Matthew Jones

Apparently not.

显然不是。

I found about five different blog posts all saying SQLServer (in various versions) does not support Deferrable Constraints.

我发现大约五篇不同的博客文章都说 SQLServer（在各种版本中）不支持可延迟约束。

On the other hand, I also found a post which attempts to mimic this feature by using "persisted computed columns,"(scroll to the last entry), but caveat emptor

另一方面，我还发现了一篇试图通过使用“持久计算列”（滚动到最后一个条目）来模仿此功能的帖子，但请注意 emptor

回答by onedaywhen

It sounds like the problem you have is that SQL does not support what Date and Darwen call 'multiple assignment'. Standard SQL's response to this was 'deferrable constraints', which SQL Server does not support. A SQL Server FK or CHECK constraint can be flagged with NOCHECK but its not quite the same. For more details see MSDN: ALTER TABLE (Transact-SQL).

听起来您遇到的问题是 SQL 不支持 Date 和 Darwen 所说的“多重赋值”。标准 SQL 对此的响应是“可延迟约束”，SQL Server 不支持该约束。可以使用 NOCHECK 标记 SQL Server FK 或 CHECK 约束，但它们并不完全相同。有关更多详细信息，请参阅 MSDN：ALTER TABLE (Transact-SQL)。

回答by bitterman0

There's a method to work around the missing deferred constraint enforcement under certain conditions(as of January 2017, there's no support for deferred constraints in SQL Server). Consider the following database schema:

有一种方法可以解决某些条件下缺少的延迟约束强制执行（截至 2017 年 1 月，SQL Server 中不支持延迟约束）。考虑以下数据库架构：

Disclaimer: The quality of the schema, or the use case, is not up for a debate here, it is given as a basic example for the workaround

免责声明：模式的质量或用例的质量不在这里讨论，它是作为解决方法的基本示例给出的

CREATE TABLE T (Id TYPE NOT NULL PRIMARY KEY, NextId TYPE NOT NULL);

ALTER TABLE T WITH CHECK ADD CONSTRAINT FK_T2T 
FOREIGN KEY (NextId) REFERENCES T (Id);

CREATE UNIQUE NONCLUSTERED INDEX UC_T ON T (NextId);

Where TYPE is some suitable data type for a surrogate key. The assumption is that the value for the surrogate key is assigned by the RDBMS during the INSERT operation (i.e. IDENTITY).

其中 TYPE 是代理键的一些合适的数据类型。假设代理键的值是在 INSERT 操作（即 IDENTITY）期间由 RDBMS 分配的。

The use case is to keep the "latest" version of the entity T with NextId = NULL, and store the previous versions by maintaining a single-linked list T.NextId -> T.Id.

用例是使用 NextId = NULL 保留实体 T 的“最新”版本，并通过维护单链表 T.NextId -> T.Id 来存储以前的版本。

Obviously, the given schema is subject to the deferred constraint problem because the insert of the new-"latest" version must precede the update of the old-"latest" and during that time there will be two records in the database with the same NextId value.

显然，给定的模式受到延迟约束问题的影响，因为新“最新”版本的插入必须先于旧“最新”版本的更新，在此期间数据库中将有两条记录具有相同的 NextId价值。

Now, if:

现在，如果：

The data type of the primary key doesn't have to be numeric, and can be calculated in advance (i.e. UNIQUEIDENTIFIER), then the deferred constraint problem is sidestepped using MERGE statement, like so:

主键的数据类型不必是数字，可以提前计算（即UNIQUEIDENTIFIER），然后使用MERGE语句回避延迟约束问题，如下所示：

DECLARE @MergeTable TABLE (Id UNIQUEIDENTIFIER);

DECLARE @NewLatestVersion UNIQUEIDENTIFIER = NEWID();

INSERT INTO @MergeTable (Id) VALUES (@NewLatestVersion);
INSERT INTO @MergeTable (Id) VALUES (@OldLatestVersion);

MERGE INTO T
USING @MergeTable m ON T.Id = m.Id
WHEN MATCHED THEN UPDATE SET T.NextId = @NewLatestVersion
WHEN NOT MATCHED THEN INSERT (Id) VALUES (@NewLatestVersion);

Apparently, MERGE statement completes all data manipulations before checking the constraints.

显然，MERGE 语句在检查约束之前完成了所有数据操作。

回答by ??ng Khoa

You can use this method

你可以使用这个方法

ALTER TABLE your_table NOCHECK CONSTRAINT your_constraint

your action

你的行动

ALTER TABLE your_table WITH CHECK CHECK CONSTRAINT ALL

回答by Mirko Klemm

If you have your own ORM layer, one solution to your problem could be separating object update from reference update by the logic of your ORM layer. Your ORM would then work with transactions based on your client-side change set in several steps:

如果您有自己的 ORM 层，则问题的一种解决方案可能是通过 ORM 层的逻辑将对象更新与引用更新分开。然后，您的 ORM 将根据您的客户端更改集分几个步骤处理事务：

1. Delete all foreign key references defined by your change set as being deleted, i.e. set corresponding foreign key columns to NULL, or, for relationships using mapping tables, DELETE entries from mapping tables as appropriate.
2. Delete all objects defined as "deleted" by your change sets
3. Create all new objects in your change set, but do not yet set foreign key columns
4. Update all "primitive" value changes on any updated objects in the change set, i.e. do not update foreign key columns
5. Set foreign key column values as defined in your change set.
6. Add mapping table mappings for mapping table-based relationships
7. Commit

1. 删除由更改集定义为已删除的所有外键引用，即将相应的外键列设置为 NULL，或者对于使用映射表的关系，根据需要从映射表中删除条目。
2. 删除您的更改集定义为“已删除”的所有对象
3. 在更改集中创建所有新对象，但尚未设置外键列
4. 更新更改集中任何更新对象的所有“原始”值更改，即不更新外键列
5. 设置更改集中定义的外键列值。
6. 添加映射表映射以映射基于表的关系
7. 犯罪

This should solve your problem, since all objects referenced exist at any time a foreign key value is set...

这应该可以解决您的问题，因为在设置外键值时，所有引用的对象都存在...