SQLServer: How to link fixed values ​​to the column?

advertisements

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


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)