SQL - CHECK


Advertisements

Previous Page Next Page

The SQL CHECK constraint is used to ensures that all values in a column of a table satisfies certain conditions. By defining a CHECK constraint on a table it limits the values that can be entered for this column. Using AND and OR operator, a CHECK constraint can constructed in such a way that can limit the values in certain columns based on values in other columns in the row.

SQL CHECK constraint with CREATE TABLE statement

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which CHECK constraint is applied on column Age.

SQL Server / Oracle / MS Access

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT CHECK (Age >= 21),
  Salary DECIMAL(18,2)
);

MySQL

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CHECK (Age >= 21)
);

To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say City and Age), the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CONSTRAINT CHK_Employee CHECK(Age >= 21 AND City = 'London')
);

SQL CHECK constraint with ALTER TABLE statement

In the above example, a table called Employee is created. To enforce CHECK constraint on Age column, the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
ADD CHECK (Age >= 21);

To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say Age and City), the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
ADD CONSTRAINT CHK_Employee 
CHECK (Age >= 21 AND City = 'London');

DROP CHECK constraint

To drop CHK_Employee CHECK constraint from table called Employee, the SQL code is given below:

MySQL

ALTER TABLE Employee
DROP CHECK CHK_Employee;

SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT CHK_Employee;

Previous Page Next Page
Advertisements