将varchar()列限制为特定值?

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”、“annual”

Is this possible to set within the SQL Server Management Studio when creating the table?

在创建表时，是否可以在SQL Server Management Studio中进行设置?

4 个解决方案

#1

Have you already looked at adding a check constraint on that column which would restrict values? Something like:

您是否已经考虑过在该列上添加一个检查约束以限制值?喜欢的东西:

CREATE TABLE SomeTable
(
   Id int NOT NULL,
   Frequency varchar(200),
   CONSTRAINT chk_Frequency CHECK (Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly'))
)

#2

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.

检查约束确定逻辑表达式的有效值，该逻辑表达式不基于另一列中的数据。例如，可以通过创建一个检查约束来限制薪资列的值范围，该约束只允许包含从$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.

您还可以使用标量函数实现检查约束，如上面的链接所描述的，这是我喜欢做的。

#3

Personally, I'd code it as tinyint and:

就我个人而言，我将它编码为tinyint并且:

Either: change it to text on the client, check constraint between 1 and 4
要么:将它更改为客户端上的文本，检查1和4之间的约束
Or: use a lookup table with a foreign key
或者:使用带有外键的查找表

Reasons:

原因:

It will take on average 8 bytes to store text, 1 byte for tinyint. Over millions of rows, this will make a difference.

它平均需要8个字节来存储文本，1个字节用于tinyint。通过数百万行，这将产生不同。
What about collation? Is "Daily" the same as "DAILY"? It takes resources to do this kind of comparison.

排序呢?“Daily”和“Daily”一样吗?做这种比较需要资源。
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.

最后，如果您想要添加“双周”或“每小时”怎么办?当您可以向查找表添加新行时，这就需要对模式进行更改。

#4

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.

当您编辑一个表时，右键单击->检查约束->添加->在表达式字段中输入频率('Daily'， 'Weekly'， 'Monthly'， ' annual ')，在(name)字段中输入一个好的约束名。你是做。

#1