SQL CHECK Constraint

The SQL CHECK constraint is used to limit the value range that can be placed in a column.
If you define a SQL CHECK constraint on a single column it allows only certain values for this column.
If you define a SQL CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "E_Salary" column when the "Employee" table is created. The CHECK constraint specifies that the column "E_Salary" must only include integers greater than 10000.
Oracle / SQL Server / MS Access:
CREATE TABLE Employee
(
E_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
E_Salary int CHECK  (E_Salary>10000)
)

MySQL:
CREATE TABLE Employee
(
E_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
E_Salary int,
CHECK (E_Salary>10000)
)

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "E_Salary" column when the table is already created, use the following SQL:
Oracle / SQL Server / MySQL / MS Access:
ALTER TABLE Employee
ADD CHECK (E_Salary>10000)
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
Oracle / SQL Server / MySQL / MS Access:
ALTER TABLE Employee
ADD CONSTRAINT chk_Employee CHECK (E_Salary>10000)

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:
Oracle / MySQL / MS Access:
ALTER TABLE Employee
DROP CONSTRAINT chk_Employee
MySQL:
ALTER TABLE Employee
DROP CHECK chk_Employee



  Tutorials

Popular Links

Contact Us

address Plot No-741,2ND Floor
Opp. Bhagabati Temple,Jayadev Vihar
      Bhubaneswar-751013
      Email: info@silantechnology.com
      Phone: 0674-2361252
address
39877, sundale dr, apt#101, Fremont, California 94538, USA
       Phone: +1(262)388-7619

facebook twitter google linked in

© 2018 Silan Technology. All Rights Reserved