How do I create a list of default values in a SQL Server 2008 R2 column? I want a column to have the default values of easy, medium, expert.
1
-
What do you mean by "default values"? Allowable values, like MySQL ENUMs?Tomalak– Tomalak2011-01-08 17:02:30 +00:00Commented Jan 8, 2011 at 17:02
Add a comment
|
2 Answers
Unlike MySql, SQL Server doesn't have enum types that you can constrain a column with ease.
The closest you can come is use a CHECK constraint.
3 Comments
Tomalak
Other than "but it automagically makes those nice little dropdowns in PHPMyAdmin" I don't see that much actual benefit in ENUM anyway.
Oded
@Tomalak - Agreed. I would normally simply use a lookup table and FK constraint.
Tomalak
Yes. That would also lead to "no structural database changes necessary to allow another value", which in turn leads to "unprivileged account (i.e. web server) can add new values", among other things, like being better-suited for i18n or indexing (and "free choice of the underlying data type and value", of course).
An an example, imagine a table of different math tests with a difficulty rating which must be either 'easy', 'medium' or 'expert'
CREATE TABLE Test
(
MathTestID smallint NOT NULL,
DifficultyRating varchar(6) NOT NULL,
CHECK (DifficultyRating In ('Easy', 'Medium','Expert'))
)
INSERT INTO Test
VALUES (1, 'Easy') //Works...
INSERT INTO Test
VALUES (1, 'Medium') //Works...
INSERT INTO Test
VALUES (1, 'Expert') //Works...
INSERT INTO Test
VALUES (1, 'Genius') //Fails...INSERT statement conflicted with CHECK constraint
