CHECK CONSTRAINT文本由SQL Server更改。如何避免或解决此问题？ [英] CHECK CONSTRAINT text is changed by SQL Server. How do I avoid or work around this?

问题描述

当我在表上定义 CHECK CONSTRAINT 时，我发现存储的条件子句可能与我输入的不同。

示例：

When I define a CHECK CONSTRAINT on a table, I find the condition clause stored can be different than what I entered.
Example:

Alter table T1 add constraint C1 CHECK (field1 in (1,2,3))

查看存储内容：

select cc.Definition from sys.check_constraints cc
inner join sys.objects o on o.object_id = cc.parent_object_id
where cc.type = 'C' and cc.name = 'T1';

我看到：

([field1]=(3) OR [field1]=(2) OR [field1]=(1))

虽然这些是等价的，但它们不是相同的文本。
（使用 BETWEEN 子句时会发生类似的行为）。

Whilst these are equivalent, they are not the same text. (A similar behaviour occurs when using a BETWEEN clause).

不是发生的是，我试图程序化地确保我所有的 CHECK 约束是正确的通过比较我将用来定义
约束的文本与存储在 sys.check_constraints - 如果不同，则删除并重新创建约束。

My reason for wishing this did not happen is that I am trying to programatically ensure that all my CHECK constraints are correct by comparing the text I would use to define the constraint with that stored in sys.check_constraints - and if different then drop and recreate the constraint.

但是，在这些情况下，因此程序总是认为需要重新创建约束。

However, in these cases, they are always different and so the program would always think it needs to recreate the constraint.

问题是：

1. 有什么已知的原因为什么SQL Server做这个翻译？它只是删除一点句法糖和存储的条款在一个更简单的形式？


2. 有一种方法可以避免这种行为（除了在long形式中写我的约束条款以匹配什么SQL Server会更改它）？


3. 是否有另一种方法来判断我的检查限制是否过期，需要重新创建？

推荐答案

SQL Server是否有任何已知的原因？它只是删除了一些语法糖并以更简单的形式存储该子句？

Is there any known reason why SQL Server does this translation? Is it just removing a bit of syntactic sugar and storing the clause in a simpler form?

我不知道有任何原因在在线图书或其他地方。但是，我的猜测是，它为SQL Server内部的某些目的标准化。它可能允许SQL Server在定义表达式方面有点宽松（例如对于列名使用 Database ），但保证列名始终适当地转义引擎需要解析表达式（即 [Database] ）。

I'm not aware of any reasons documented in the Books Online, or elsewhere. However, my guess is that it's normalized for some purposes that are internal to SQL Server. It might allow SQL Server to be a bit lenient in defining the expression (such as using Database for a column name), but guaranteeing that the column names are always appropriately escaped for whatever engine needs to parse the expression (ie, [Database]).

有一种方法可以避免行为（除了在长形式中写我的约束条款以匹配什么SQL Server将更改它）？

Is there a way to avoid the behaviour (other than to write my constraint clauses in the long form to match what SQL Server would change it to)?

可能不是。但是如果你的约束不是很复杂，重写约束条款在长的形式这样一个坏主意？

Probably not. But if your constraints aren't terribly complicated, is re-writing the constraint clauses in the long form such a bad idea?

有

Is there another way to tell if my check constraint is 'out of date' and needs recreating?

在我直接回答这个问题之前，我会指出这里有一些编程哲学。 SQL Server为CHECK约束的文本提供的API仅保证您将获得与原始表达式等效的 。虽然你当然可以构建一些奇特的方法，以确保你将永远能够重现SQL Server的表达式的规范化版本，但不能保证Microsoft将来不会改变它的规范化规则。事实上，可能不能保证两个等效的表达式将始终相同地归一化！

Before I answer this directly, I'd point out that there's a bit of programming philosophy involved here. The API that SQL Server provides for the text of a CHECK constraint only guarantees that you'll get something equivalent to the original expression. While you could certainly build some fancy methods to try to ensure that you'll always be able to reproduce SQL Server's normalized version of the expression, there's no guarantee that Microsoft won't change its normalization rules in the future. And indeed, there's probably no guarantee that two equivalent expressions will always be normalized identically!

因此，我首先建议您重新检查您的体系结构，看看是否您可以完成相同的结果，而不必依赖于未记录的API行为。

So, I'd first advise you to re-examine your architecture, and see if you can accomplish the same result without having to rely on undocumented API behavior.

有这样一来，有一些方法在这个问题（和答案）。

Having said that, there are a number of methods outlined in this question (and answer).

另一个替代方案，这是一个更加暴力但可能可以接受，将总是假设表达式是过时的每次检查时重新创建约束。除非你期望这些约束经常变得过时（或表格相当大），看起来这是一个不错的解决方案。你甚至可能在一个事务中运行它，所以如果新的约束已经被违反，只需回滚事务并报告错误。

Another alternative, which is a bit more brute-force but perhaps acceptable, would be to always assume that the expression is "out of date" and simply drop/re-create the constraint every time you check. Unless you're expecting these constraints to frequently become out-of-date (or the tables are quite large), it seems this would be a decent solution. You could probably even run it in a transaction, so that if the new constraint is already violated, simply roll-back the transaction and report the error.

这篇关于CHECK CONSTRAINT文本由SQL Server更改。如何避免或解决此问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！