Restrict varchar() column to specific values?

Question

Is there a way to specify, for example 4 distinct values for a varchar column in MS SQL Server 2008?

For example, I need a column called Frequency (varchar) that only accepts 'Daily', 'Weekly', 'Monthly', 'Yearly' as possible values

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

Answer 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')) ) 

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

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.