SQLServer: How To bind fixed values to Column?

Question

Say I defined a char column Type. I want to strict its value to say for example (Black, White, Red, Blue) Only...

How can I do that??

All i know, this is easy in Access :P

Answer 1

If there are just a few permitted values then you can use a CHECK constraint:

ALTER TABLE dbo.Your_Table
ADD CONSTRAINT CK_YourTable_YourColumn
    CHECK (Your_Column IN ('Black', 'White', 'Red', 'Blue'))

If there are more values then you can use a lookup table and a FOREIGN KEY constraint:

CREATE TABLE dbo.Lookup_Colours (Colour VARCHAR(10))
-- then populate Lookup_Colours with all permitted values

ALTER TABLE dbo.Your_Table
ADD CONSTRAINT FK_YourTable_YourColumn
    FOREIGN KEY (Your_Column)
    REFERENCES dbo.Lookup_Colours (Colour)

Answer 2

You need a rule on the column

And yes , it's as easy as it is in access.

Answer 3

You should either use a Rule (as was pointed out Rules are now deprecated instead constraints should be used) or a foreign key to a Color table containing the Colors you allow.

A Check constraint can be created like this:

ALTER TABLE MyTable
ADD CONSTRAINT CK_MyTable_ColorType
    CHECK (ColorType IN ('Black', 'White', 'Red', 'Blue'))

If you want to go with a table, then you should create a 'Color' table with the colorName and an ID. On the table(s) that need the reference you should add a column with the foreign key to the 'Color'-table ID.

To reference the 'Color'-table you have to use a join e.g.

SELECT *
FROM   MyTable INNER JOIN
         ColorTable ON MyTable.ColorID = ColorTable.ID

Updated: With Constraint instead of Rule older Databases can still use Rules though (2000).

CREATE RULE Color_Rule
AS 
@list IN ('Black', 'White', 'Red', 'Blue')

Hope it helps

Answer 4

One way to do this would be to create a separate table "Type" and put your values there. The table that use to have the Type varchar, would now have a FK TypeID that points to the other table.

This will require an extra join, but will give you control over what strings can be Types.