SQL Check Constraint
This article will help you to pratice and devlop your concept for SQL CHECK Constraint.SQL ‘CHECK’ constraint are used in creating a table , and altering a table. It is used to limit the range of values that can be placed in columns.
‘CHECK’ constraint provides us with a condition , if condition fails then values are discarded and not added accordingly.
SQL CHECK on Create Table
As already discussed , SQL CHECK constraint refines the value. Let us understand the concept with help of some examples.
Example 1: A table ‘Harsh’ is created.
CREATE TABLE harsh
{
S.no int NOT NULL,
state varchar(255) NOT NULL,
Area int ,
Region varchar(255) NOT NULL
Literacy rate int,
CHECK(Literacy rate>=65)
}
In the following example a table is created with entities -S.NO, State, Literacy rate. SQL CHECK constraint helps Database Administrator to refine values. Only (Literacy rate>65)are accepted.
Table(#example 1): Table ‘Harsh’ is created.
| S.NO | State | Area | Literacy rate(%) | Region |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | Southern |
| 2 | Maharashtra | 307713 | 83.78 | Western |
| 3 | Rajasthan | 342239 | 67.06 | Western |
| 4 | Odisha | 155707 | 73.45 | Eastern |
| 5 | West Bengal | 88752 | 77.08 | Eastern |
| 6 | Jharkhand | 79714 | 67.63 | Eastern |
Example 2:
To allow constraint on more than one column:
CREATE TABLE harsh ( S.no int NOT NULL, state varchar(255) NOT NULL, Area int , Region varchar(255) NOT NULL Literacy rate int, CONSTRAINT CHK_harsh CHECK (Literacy rate>=70 AND Country='India') );
Table:
| S.NO | State | Area | Literacy rate(%) | Country |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | India |
| 2 | Maharashtra | 307713 | 83.78 | India |
| 3 | Rajasthan | 342239 | 67.06 | India |
| 4 | Odisha | 155707 | 73.45 | India |
| 5 | West Bengal | 88752 | 77.08 | India |
SQL CHECK on alter table:
SQL CHECK constraint could also be used with ALTER table command. It could add conditions to predefined table.
Example:
ALTER TABLE harsh ADD CHECK(Country='India');
Table:
| S.NO | State | Area | Literacy rate(%) | Country |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | India |
| 2 | Maharashtra | 307713 | 83.78 | India |
| 3 | Rajasthan | 342239 | 67.06 | India |
| 4 | Odisha | 155707 | 73.45 | India |
| 5 | West Bengal | 88752 | 77.08 | India |
DROP a CHECK Constraint:
A constraint can be installed and can be deactivated at any point of time. Let us look at some example at learn the concept to ‘How to DROP a CHECK constraint’?
Example:
ALTER TABLE Persons DROP CHECK CHK_Country;
Following command will remove the CHECK condition. DROP CHECK CHK_Country[ALL restrictions to the 'Country' columns is removed].
Hope that you like this article , please drop your comments down below and show us love.For any doubts , you can comment down below.