How to create a constraint that allows 2 options using SQL Server?

Question

Could anyone help saying how I could create a constraint that allows 2 options using SQL Server?

For exapmle, I want it to be possible to add new candidates only if they have an intermediate or advanced knowledge in SQL.

So, I coded like below, but it doesn’t work.

CREATE TABLE Candidates
( 
    Name VARCHAR(150) NOT NULL UNIQUE, 
    BirthDate DATE NOT NULL,
    SQLlevel VARCHAR(100) NOT NULL 
        CHECK (SQLlevel = 'Intermediate' OR 'Advanced') 
)

Thanks in advance.

Answer 1

To explain why what you had didn't work it's because of your boolean expression:

SQLlevel = 'Intermediate' OR 'Advanced'

Your expression after the OR isn't a boolean expression, it's just a literal. You have to define the comparison operator each time. If you wanted to use a OR you would need to do the following:

CREATE TABLE Candidates (Name varchar(150) NOT NULL UNIQUE,
                         BirthDate date NOT NULL,
                         SQLlevel varchar(100) NOT NULL CHECK (SQLlevel = 'Intermediate' OR SQLlevel = 'Advanced'));