SQL CHECK Constraint

What is a CHECK Constraint in SQL?

The CHECK constraint in SQL is used to limit the values that are being inserted into a table. This limit is specified using a condition that is written after the CHECK keyword in SQL. The table will insert only the values that satisfy the given condition or else, the data will not be inserted into the table.

However, this CHECK constraint is defined on a column for which you have to restrict the values in a table. 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 people who are eligible to vote therefore, we restrict the age column using the CHECK constraint in SQL.

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 a record in this table that is invalid such as the age which does not satisfy the CHECK constraint then it will throw an error and will not enter this value into the table.

Let us see it with an example to insert 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 in SQL while creating a table or after creating a table as well. 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 as well in the table.

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 will be inserted into this table from Bangalore city.

However, we can also name a CHECK constraint in SQL and can 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 you did not feel the 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 is used to define the CHECK constraint while creating the table and the ALTER command is used to define the CHECK constraint after the table has already been created.

The ALTER statement in SQL is used to update the table. The CHECK constraint is 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 with it. Therefore, you will execute the following query to use the ALTER command in SQL:

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

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 who live in Bangalore and their salary is greater than 80000 are inserted into the table.

SQL CHECK Constraint and NULL Values

Null refers to the unknown values. The CHECK constraint in SQL accepts the null values. If we have a value null, then the CHECK constraint evaluates the value as unknown and accepts it, and, therefore, null is inserted into the table.

Let's say in the Employee table, we do not know the city of a particular employee, therefore, we insert null at that place. It will accept the null value and will insert it into the table.

Output:

Therefore, the best way to avoid such a situation is to use the NOT NULL constraint in SQL which will insert the value into the table only when the data is not null.

Let us say we have a table, Employee which has three columns ID, Name, and PhoneNo of the customer. We apply the NOT NULL keyword with every column so that it could not accept the NULL values into the table.

We are creating the table below:

Now, if we insert a value into the table which is NULL, it will not be inserted into the table because of the NOT NULL constraint written with the columns of the table. The NOT NULL constraint will not allow NULL values into the table.

Let us insert such a value into the table:

Output:

Therefore, it throws an error stating that the specified column cannot be null.

Now, let us insert valid data into the table:

Output:

Therefore, now the data will be successfully inserted into the table.

SQL CHECK Constraint and User-Defined Functions

We can define user-defined in SQL and use them with the CHECK constraint to restrict the values to be inserted into our table. We define these user-defined functions to validate the data to be entered into the table.

Let us define a user-defined function DiffofDates(), which calculates the difference between two dates using the DATEDIFF() function of SQL.

Now let us assume we have a table Employee which has id, Name, and the joining data as well as the ending date in the company of the employee. Lets us define a CHECK constraint on it such that we need to insert only those employees' data into the table who have worked for the company for more than 10 years.

Now, when we insert the following data into our table Employee, it will be successfully entered into our table as the difference between the dates is more than 10 years.

In this way, we can use our CHECK constraint with the user-defined functions.

DROP a CHECK Constraint

The DROP statement is used to delete a table in SQL. However, the DROP statement can also be used to 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 on it 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.

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

Output:

In this way, we can drop the CHECK constraint in a table.