Limit SQL Server column to a list of possible values

user_1253708 0
Viewed 62241

How do I put a constraint on a column so that it can only contain the following values? What do you call this type of constraint?

Allowed values: "yes", "no" or "maybe"
Column Data Type: nvarchar(5)
DBMS: SQL Server 2008
constraints sql-server database
4 Answers

Yes, check a constraint is it what you need. You can declare check constraint at the table declaration:

CREATE TABLE test(
    _id BIGINT PRIMARY KEY NOT NULL,
    decision NVARCHAR(5),
    CHECK (decision in ('yes','no','maybe'))
);
user_6296850

2021 here, I wonder if we can do this in another way (not necessarily a better way compared to check constraints).

For the column that only allows for "yes", "no" or "maybe" values maybe we can have a column with bit type and then have a computed column next to it like

Create Table xxx (
ColumnName bit null,
ColumnNameDerived varchar(10) as 
    case ColumnName
        when 0 then 'no'
        when 1 then 'yes'
        else 'maybe'
    end
)

For column with more possible values maybe choose tinyint type and then with a check constraint like "<= 10". Say a column has 10 possible values then a constraint "< 10" will do.

so the constraint will not be too long to read and immediately you know how many possible values are there. This can help save storage too.

user_91587140
Related