SQL Check Constraints With Example

.

 SQL Check Constraint - How to use SQL Check Constraints?

Check Constraints in SQL With Example

What are the Check Constraints in SQL?

How to use Check Constraints 

Add- Check Constraints 

How to use SQL Check on Create Table?

Let's take an example to understand the check constraints while creating a table suppose the user wants to create an employee table and the age of the employee must be 18 or older then we use the below SQL queries.  

MySQL:

CREATE TABLE Employee ( EID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );

SQL Server / Oracle / MS Access:

CREATE TABLE Employee ( EID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );

How to use Check Constraints on Multiple Columns (Add)

While creating a table in the database user can define check constraints on multiple columns, Just use the following syntax for defining check on multiple constraints. 

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Employee ( EID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Lucknow') );

Alter - Check Constraint 

How to use alter for check constraints?

MySQL / SQL Server / Oracle / MS Access:

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

How to use Check Constraints on Multiple Columns (Alter)

ALTER TABLE Employee ADD CONSTRAINT CHK_employeeAge CHECK (Age>=18 AND City='Lucknow');

Drop - Check Constraints

 

How to drop a check constraint?

 

SQL Server / Oracle / MS Access:

ALTER TABLE Employee DROP CONSTRAINT CHK_EmployeeAge;

Hope!!! The above tutorial on Check Constraints in SQL with the example " is helpful for you...

Team,

QA acharya