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

有没有一种方法来指定，例如在 MSSQLServer2008中为 varchar 列指定4个不同的值？

例如，我需要一个名为 Channel (varchar)的列，该列只接受“ Daily”、“ Weekly”、“ Moonly”和“ Yearly”作为可能的值

是否可以在创建表时在 SQLServerManagementStudio 中进行设置？

136250 次浏览
小开

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.

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.

小开
最佳答案

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'))
)
小开

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.

小开

Personally, I'd code it as tinyint and:

  • Either: change it to text on the client, check constraint between 1 and 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.

  • 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.


提交答案