Restrict varchar() column to specific values?
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')))
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.
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.