SQL 将 varchar() 列限制为特定值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2441427/
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
Restrict varchar() column to specific values?
提问by Adam
Is there a way to specify, for example 4 distinct values for a varchar column in MS SQL Server 2008?
有没有办法为 MS SQL Server 2008 中的 varchar 列指定例如 4 个不同的值？
For example, I need a column called Frequency (varchar) that only accepts 'Daily', 'Weekly', 'Monthly', 'Yearly' as possible values
例如，我需要一个名为 Frequency (varchar) 的列，它只接受“Daily”、“Weekly”、“Monthly”、“Yearly”作为可能的值
Is this possible to set within the SQL Server Management Studio when creating the table?
创建表时，是否可以在 SQL Server Management Studio 中进行设置？
回答by Ashish Gupta
Have you already looked at adding a check constrainton that column which would restrict values? Something like:
您是否已经考虑在check constraint该列上添加一个会限制值的？就像是：
CREATE TABLE SomeTable
(
Id int NOT NULL,
Frequency varchar(200),
CONSTRAINT chk_Frequency CHECK (Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly'))
)
回答by Michael Petrotta
You want a check constraint.
你想要一个检查约束。
CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.
CHECK 约束从不基于另一列中的数据的逻辑表达式中确定有效值。例如，可以通过创建 CHECK 约束来限制工资列的值范围，该约束仅允许范围从 $15,000 到 $100,000 的数据。这可以防止输入超出常规工资范围的工资。
You want something like:
你想要这样的东西：
ALTER TABLE dbo.Table ADD CONSTRAINT CK_Table_Frequency
CHECK (Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly'))
You can also implement check constraints with scalar functions, as described in the link above, which is how I prefer to do it.
您还可以使用标量函数实现检查约束，如上面链接中所述，这是我更喜欢的方式。
回答by gbn
Personally, I'd code it as tinyint and:
就个人而言，我将其编码为 tinyint 并且：
- Either: change it to text on the client, check constraint between 1 and 4
- Or: use a lookup table with a foreign key
- 要么：在客户端将其更改为文本，检查 1 和 4 之间的约束
- 或者：使用带有外键的查找表
Reasons:
原因：
It will take on average 8 bytes to store text, 1 byte for tinyint. Over millions of rows, this will make a difference.
What about collation? Is "Daily" the same as "DAILY"? It takes resources to do this kind of comparison.
Finally, what if you want to add "Biweekly" or "Hourly"? This requires a schema change when you could just add new rows to a lookup table.
存储文本平均需要 8 个字节，tinyint 需要 1 个字节。超过数百万行，这将有所作为。
整理呢？“天天”和“天天”一样吗？进行这种比较需要资源。
最后，如果您想添加“双周”或“每小时”怎么办？当您可以将新行添加到查找表时，这需要架构更改。
回答by Denis K
When you are editing a table
Right Click -> Check Constraints -> Add -> Type something like Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly')in expression field and a good constraint name in (Name) field.
You are done.
当您编辑表时，
右键单击 -> 检查约束 -> 添加 ->Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly')在表达式字段中键入类似内容，并在（名称）字段中键入一个好的约束名称。
你完成了。
