SQL CHECK CONSTRAINT

SQL CHECK CONSTRAINT example – Learn how to limit value range for columns in the table in MySQL database with example and important points.

POINTS TO NOTICE

1. The check constraint used to limit the value range that can be placed in a column.
2. Defining a check constraint (Boolean expression) on a single column, it allows only certain values which satisfies the Boolean expression.
3. Defining a check constraint on table it can limit values in certain columns based on values in other in the row.

 SQL syntax to create CHECK constraint

We can create CHECK constraint on column of a table while creating a table using create table statement . Below are two syntax given by which you can put CHECK constraint on column of a table

Syntax:

CREATE TABLE [table_name]
(
column1 column1_definition ,
column2 column2_definition ,
……………………..
check  [expression]

);

or

CREATE TABLE [table_name]
(
column1 [column1_definition]   check  [expression] ,
column2 [column2_definition] ,
……………………..

);

SQL Check Constraint Example

In this example query, we have put the CHECK constraint on address book id column that allow to store only positive value.

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT  ,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254)  default   ‘JAVA’,
check     ADDRBOOK_ID>0

);

In the above SQL query , the ADDRBOOK_ID column  allows to store only > 0 values.

SQL syntax to define check constraint using alter table statement.

ALTER TABLE [table_name]
check  expression;

OR

ALTER TABLE [table_name]
ADD CONSTRAINT [constraint_name] check expression.

here is the example query to define check constraint using alter table statement

ALTER TABLE ADDRESSBOOK
CHECK ADDRBOOK_ID>0;

SQL syntax to Drop CHECK Constraint

SYNTAX:

ALTER TABLE [table_name]
DROP CHECK [constraint_name];