Two questions in SQL Server

Question

1. How do I define in SQL Server in datatype field with fixed data (like SELECT in html) of my choice?
2. How to set column, which does not allow a number greater than 50, in Management Studio??

Thanks!

Answer 1

Even though this post contains two questions (Which is usually a problem in Q & A websites), I believe that the answer to both question can be the same (otherwise I wouldn't post this answer to begin with) - but that's assuming I understand the first question - which is basically how to define a column that can only contain a limited set of options (like a dropdown or an enum).

Based on that assumption, and also assuming we're not talking about a very large list of values, both questions can be answered by using a check constraint.

In SQL Server, a check constraint validates data that is attempted to be entered into a table (either by insert or by update), and throws an exception if the check returns false.

So, Limiting the set of values a column can take in is a good candidate for a check constraint - unless we're talking about a very large list of values, which makes it cumbersome to write and maintain and is usually better covered using a foreign key.

So, assuming I want a table that have two columns - one a string that can only have the values yes, no, black and white, and the other an int that can have values between 0 and 50 (inclusive) I would write it like this:

CREATE TABLE DemoTable 
(
    Enum varchar(5),
    CONSTRAINT CHK_DemoTable_Enum CHECK (Enum IN('Yes', 'No', 'Black', 'White')),
    Number tinyint,
    CONSTRAINT CHK_DemoTable_Number CHECK (Number >= 0 AND Number <= 50)
);

You can see a live demo on rextster.