SQL CHECK Constraint

In this changing era, one of the most essential things in today's world is our data. This data must be stored and organized to be easily retrieved. The place where our data is stored is known as a database. Inside these databases, we have multiple tables in which our data is stored as rows and columns.

The data is inserted into these tables using the INSERT INTO command in SQL. However, sometimes, we need to insert our data only if a particular condition is satisfied. In such situations, we use the CHECK constraint in SQL.

The Check Constraints in SQL inserts the data only when a particular condition is satisfied; otherwise, it throws an error.

CHECK Constraint in SQL

Check Constraints in SQL limits the values inserted into a table. This limit is specified using a condition written after the CHECK keyword in SQL. The table will insert only the values that satisfy the given condition; otherwise, the data will not be inserted into the table.

However, this CHECK constraint is defined on a column for which you must restrict the table values. It can be defined on a single column or several columns as well as per your need.

For example - let us say we are creating a table Voters which contains the name of the people, their residing city, and their age. Now, we want to insert only the data of the eligible people to vote. Therefore, we restrict the age column using the CHECK constraint.

You will execute the following query for creating the Voters table:

• The above query will create the table Voters and will only enter the records of people who are eligible to vote, that is, equal to or above the age of 18 years.

However, if we enter an invalid record in this table, such as the age that does not satisfy the Check Constraints in SQL, then it will throw an error and will not enter this value into the table.

Let us see it with an example of inserting an invalid record into the table:

Error:

Let us now insert a valid record into the table:

• This record will be inserted into the table.

You can apply this CHECK constraint while creating or after creating a table. However, how it is done is discussed in further topics.

SQL CHECK on CREATE TABLE

The CREATE statement in SQL is used to create a table. The CHECK constraint can be defined while creating a table as a result of which only those data will be inserted into the which is valid.

Let us say we have a table Employee in which we only have to insert the data when the employee is from Bangalore. However, we have other columns in the table as well.

Therefore, we need to execute the following query to apply the CHECK constraint on the table Employee:

• Therefore, after executing this query, only those employees' data from Bangalore City will be inserted into this table.

However, we can also name a CHECK constraint and define it on multiple columns by executing the following query:

• The above query is used to define the CHECK constraint on both the City and as well as the Salary column.

SQL CHECK on ALTER TABLE

Whenever we create a table using the CREATE command, the CHECK constraint can be defined while creating it. However, if you forget or do not need to apply the CHECK constraint while creating a table, you can still define it after the table has been created, for which we use the ALTER command in SQL.

Therefore, the CREATE command defines the CHECK constraint while creating the table, and the ALTER command defines the CHECK constraint after the table has already been created.

The ALTER statement in SQL is used to update the table. The Check Constraints in SQL can be used with the ALTER command for limiting the values in a table when the table is already created.

Let us say we have the same table, Employee, which was created above, but this time, the CHECK constraint is not defined. Therefore, you will execute the following query to use the ALTER command in SQL:

• The table will insert only data from those employees who are from Bangalore.

However, you can also name a CHECK constraint and define it on multiple columns using the below query:

• Using the above query, only the data of the employees living in Bangalore whose salary is greater than 80000 are inserted into the table.

DROP a CHECK Constraint

The DROP statement is used to delete a table in SQL. However, the DROP statement can also delete a CHECK constraint from a table.

Let us consider the same table Employee, which was created above. At this point, the table has the CHECK constraint defined for the City and the Salary column; therefore, the values that do not satisfy the CHECK condition will not be inserted into the table.

The table Employee cannot insert the following data into the table. It will throw an error after running the following query because it does not satisfy the CHECK constraint condition.

Error:

Now, let us drop the CHECK constraint from this table using the following query:

• The above query deletes the CHK_Employee constraint from the Employee table. Naming the CHECK constraints in full while dropping the constraint in a table.

If we try to insert the following data into the table, it will be inserted successfully because the CHECK constraint has been removed.

Output:

• This way, we can drop the CHECK constraint in a table.