SQL Server CHECK Constraint

In SQL Server, you may create a check constraint in a table to specify the data values that are permitted in one or more columns. This means, that when you define a CHECK constraint on a column, it restricts the values that can be assigned to that column.

Some examples-

1)
The check requirement in the example below allows us to specify a salary value equal to or greater than 2000. If the end-user enters a value that is less than that, an error will occur. If the organization has a requirement or is required by law that the employee’s wage cannot be less than $2,000, then such validation is useful.

CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(100),
Job VARCHAR(100),
Mgr INT,
HireDate DATE,
Sal INT,
Comm INT,
DeptNo INT,
Sal int CHECK (Sal>=2000)
);

2)
In the example below, the check requirement allows us to provide a salary figure equal to or greater than 2000, as well as if the employee’s residency is “Dubai.” An error will occur if the end-user inputs a value that is less than the required salary value or if the employee’s home is not in Dubai. Such validation is useful if the organization has a requirement or is required by law that the employee’s wage cannot be less than $2,000 and the employee must be a local person.

CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(100),
Job VARCHAR(100),
Mgr INT,
HireDate DATE,
Sal INT,
Comm INT,
DeptNo INT,
City VARCHAR(100),
CONSTRAINT CHK_Employees CHECK (Sal >=2000 AND City=’Dubai’)
);

3)
The example below is similar to example # 2; just to show how to provide multiple values.

4)
The example below is similar to example # 2; Along with the email validation. The email should contain certain characters to pass the validation.

CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(100),
Job VARCHAR(100),
Mgr INT,
HireDate DATE,
Sal INT,
Comm INT,
DeptNo INT,
City VARCHAR(100),
Email VARCHAR(100),
CONSTRAINT CHK_Employees_Sal CHECK (Sal >=2000 AND City IN(‘Dubai’,’Abu Dhabi’)),
CONSTRAINT CHK_Employees_Email CHECK (Email LIKE ‘%_@__%.__%’)
);