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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Restrict varchar() column to specific values?

Examples related to sql

Examples related to sql-server-2008

Examples related to ssms