SQL Check Constraint :
In my previous article i have given idea of different types of constraints in SQL as well as SQL Not null Constraint with its examples. I have explained about SQL Unique Constraint with examples also. This article gives you the idea of new SQL constraint which is used to limit the value range at column level. SQL Check Constraint is mostly used in so many real life scenarios in industry to restrict the value for the specific range.
The CHECK constraint is used to limit the value range that can be placed in a column.
How to add SQL Check Constraint ?
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
Real Life Scenario:
CREATE TABLE Student
(
Class Number (5),
RollNo Number (10),
Subject Varchar2 (15),
Fees Number (10, 2) CHECK (Fees>500)
);
Example:
INSERT INTO Student (Class, RollNo, Subject, Fees)
Values (5, 105, ‘Eng’, 550);
INSERT INTO Student (Class, RollNo, Subject, Fees)
Values (5, 105, ‘Eng’, 50);
ERROR at line 1:
ORA-02290: check constraint (Amit.SYS_C004958) violated
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Student
(
Class Number (5),
RollNo Number (10),
Subject Varchar2 (15),
Fees Number (10),
CONSTRAINT C_Student_Fees_Subject CHECK (Fees>500 AND Subject=’English’)
);
Example:
INSERT INTO Student (Class, RollNo, Subject, Fees)
Values (5, 10, ‘English’, 650);
INSERT INTO Student4A (Class, RollNo, Subject, Fees)
Values (5, 10, ‘Maths’, 350);
ERROR at line 1:
ORA-02290: check constraint (Amit.C_STUDENT_FEES_SUBJECT) violated
Note: You can use AND/OR Operators in CHECK Constraint.
Example :
CREATE TABLE Student4B
(
Class Number (5),
RollNo Number (10),
Subject Varchar2 (15),
Fees Number (10),
CONSTRAINT C_Student4B_Fees_Subject CHECK (Fees>500 OR Subject=’English’)
);
Example:
INSERT INTO Student4B (Class, RollNo, Subject, Fees)
Values (5, 10, ‘English’, 50);
INSERT INTO Student4B (Class, RollNo, Subject, Fees)
Values (5, 10, ‘Maths’, 650);
INSERT INTO Student4B (Class, RollNo, Subject, Fees)
Values (5, 10, ‘Science’, 400);
ERROR at line 1:
ORA-02290: check constraint (Amit.C_STUDENT4B_FEES_SUBJECT) violated
To DROP a CHECK SQL Constraints:
To Drop a CHECK Constraint, use the following SQL.
Syntax:
ALTER TABLE <Table_Name>
DROP CONSTRAINT <Constraint_Name>;
Example:
ALTER TABLE Student
DROP Constraint SYS_C005434;
ALTER TABLE Student
DROP CONSTRAINT C_Student_Fees_Subject;
SQL CHECK Constraint on ALTER TABLE:
Syntax:
ALTER TABLE <Table_Name>
ADD <Constraint_Type> (Column_Name);
ALTER TABLE Student
ADD CHECK (RollNo>5);
OR
ALTER TABLE Student MODIFY Class Number (10) CHECK (Class>5);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
Syntax:
ALTER TABLE <Table_Name>
ADD CONSTRAINT <Constraint_Name> CONSTRAINT_TYPE (Column_Name);
ALTER TABLE Student
ADD CONSTRAINT C_Student_Subject_Fees CHECK (Subject=’Hindi’ AND Fees>500);
ALTER TABLE Student
ADD CONSTRAINT C_Student_Subject_Fees CHECK (Subject=’Hindi’ OR Fees>500);
Hope you got the idea about the SQL Check Constraint with different real life examples. This article will help you practically understanding the SQL Check Constraint with its real examples. User will be able to use the check constraint in real life scenario. Please comment in comment section if you like this article on SQL check constraint.