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?

If it's safe to assume that there won't be any more valid values and there will be many rows in the table, I'd encode the possible values in something smaller and faster than a varchar(). — Wikser, Mar 14 '10 at 07:16

score 155 · Accepted Answer · edited Feb 08 '17 at 21:42

155

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

edited Feb 08 '17 at 21:42

Adam

1,546
2
18
23

answered Mar 14 '10 at 07:05

Ashish Gupta

14,869
20
75
134

2

Thank you - this worked great. One small change I had to make though was that "Frequency IN...." had to be enclosed in parenthesis too. SQL server studio didn't like it without for some reason. – Adam Mar 14 '10 at 08:25
1

Is this constraint check case sensitive? – RWendi May 01 '13 at 09:27
3

I think you missed a coma after the 4th line `Frequency varchar(200)` – BillOverFlow Sep 29 '16 at 01:09
RWendi - the default SQL Server collation is case-insensitive, which applies on the database level. Further info here https://stackoverflow.com/questions/1439485/how-to-determine-if-an-instance-of-sql-server-is-case-sensitive-or-not – jwoe Aug 15 '17 at 15:21

score 67 · Answer 2 · answered Mar 14 '10 at 07:05

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.

this answer was good too.. why can't we accept more than one!! :) — Adam, Mar 14 '10 at 08:26

score 15 · Answer 3 · answered Mar 14 '10 at 15:52

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.

score 6 · Answer 4 · answered Mar 14 '10 at 07:07

6

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.

answered Mar 14 '10 at 07:07

Denis K

1,448
1
10
17

Restrict varchar() column to specific values?

4 Answers4

Linked