您可以替换或更新 SQL 约束吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/861146/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you replace or update a SQL constraint?
提问by StormPooper
I have written the following constraint for a column I've called 'grade':
我为名为“等级”的列编写了以下约束：
CONSTRAINT gradeRule CHECK grade IN (‘easy', ‘moderate', ‘difficult'),
Is it possible to later update the gradeRuleto have different values? For example, 'moderate' and 'difficult' could be changed to 'medium' and 'hard'.
是否可以稍后更新gradeRule以具有不同的值？例如，“中等”和“困难”可以更改为“中等”和“困难”。
Thanks
谢谢
回答by Scott Ivey
You could drop the existing constraint, and add the new constraint with the NOCHECK option. This would allow you to add the constraint even though data in the table violates the constraint. The problem with doing this though would be that you wouldn't be able to update existing records without making them pass the constraint first.
您可以删除现有约束，并使用 NOCHECK 选项添加新约束。这将允许您添加约束，即使表中的数据违反约束。这样做的问题是，如果不让它们首先通过约束，您将无法更新现有记录。
ALTER TABLE SomeTable DROP CONSTRAINT gradeRule
GO
ALTER TABLE SomeTable ADD CONSTRAINT gradeRule ... WITH NOCHECK
GO
Although this is possible, its not usually recommended because of the potential problems with future updates of the data.
尽管这是可能的，但通常不建议这样做，因为未来数据更新可能会出现问题。
回答by Scott Ferguson
Drop the constraint, and then add the replacement constraint. You can't update a constraint in SQL Server at least.
删除约束，然后添加替换约束。至少不能在 SQL Server 中更新约束。
ALTER TABLE SomeTable DROP CONSTRAINT gradeRule
In addition, you'll need to update the table data before adding the new constraint, so that it meets the new constraint.
此外，您需要在添加新约束之前更新表数据，使其符合新约束。
回答by Mark Sherretta
If you change the constraint, all of the data currently in the table must meet the constraint. So if you had 2 rows of data with 'moderate' and tried to change the constraint to easy, medium, and hard, it would not let you.
如果更改约束，则表中当前的所有数据都必须满足约束。因此，如果您有 2 行数据为“中等”，并尝试将约束更改为简单、中等和困难，则不会让您这样做。
So you would have to make the new constraint (easy, moderate, medium, difficult, hard) or, update the data to the new values - moderate --> medium etc.
因此，您必须设置新约束（简单、中等、中等、困难、困难），或者将数据更新为新值 - 中等 --> 中等等。
