SQL - Check Constraint

---

Check constraint is useful for the validity of the data entered into the particular table columns. The check constraint is the way of providing another protecting layer of the data.

If we specify check constraint on a column, then it will allow only certain values to be inserted in this column.

A check constraint is only applied to the single column, but there is the possibility of multiple check constraints in the single table.

Adding Check constraint on a Column

To apply a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.

Syntax

Following is the syntax to specify the check constraint on column.

CREATE TABLE table_name (
   column1 datatype(size),
   column datatype(size) constraint constraintName CHECK Check(columnName    condition value),
   …, column datatype (size));

Example

In the following SQL query, we are creating a table named employees and specifying a column-level check constraint on one columns.

CREATE TABLE employees(
   EID INT NOT NULL,
   NAME VARCHAR(40),
   AGE INT NOT NULL CHECK(AGE>=20),
   CITY VARCHAR(30),
   C_Phone VARCHAR(12) NOT NULL UNIQUE);

In the above query, we have specified the check constraint on the age column. According to this constraint, the age column will allow only those records to be inserted where the age is greater than 20.

Verification

To verify the created table, we can use the following query in the SQL −

EXEC sp_help "dbo.employees";

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table.

+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| constraint_type        | constraint_name                | delete_action | update_action | status_enabled | status_for_replication |constraint_keys |
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| CHECK on column AGE    | CK__employees__AGE__4CC05EF3   | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([AGE]>=(20))   |
| UNIQUE (non-clustered) | UQ__employee__57B58178E24D3C4F | (n/a)         | (n/a)         | (n/a)          | (n/a)                  |   C_Phone      |
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+

Check constraint on multiple Columns

We can also add check constraint on multiple columns of a table In the following example, we are creating a table named students and specifying a column-level check constraint on multiple columns (AGE and FEE).

CREATE TABLE students(
   SID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT NOT NULL CHECK(AGE<=24),
   CITY VARCHAR(30),
   FEE NUMERIC NOT NULL CHECK(FEE>=15000));

According to both constraints created above, the age column will allow only those records where the age is less than 24, and the fee column will allow only those records where the fee is greater than 15,000.

Verification

To verify the created table, we can use the following query in the SQL −

EXEC sp_help "dbo.employees";

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table.

+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| constraint_type        | constraint_name                | delete_action | update_action | status_enabled | status_for_replication |constraint_keys |
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| CHECK on column AGE    | CK__employees__AGE__4CC05EF3   | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([AGE]<=(24))   |
| CHECK on column FEE    | CK__students__FEE__5090EFD7    | (n/a)         | (n/a)         | Enabled	   | Is_For_Replication     |([FEE]>=(15000))|
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+

Now, we're trying to insert values in the students table that are older than 24 and have a fee of less than 15,000.

INSERT INTO students VALUES(001, 'Aman Kumar', 25, 'Ranchi', 14000);

The query throws an error when we try to insert the data because we are utilizing the age, whose value is greater than 24.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__students__AGE__4F9CCB9E". The conflict occurred in database "master", table "dbo.students", column 'AGE'.

Adding check constraint on the table Level

We must use the check constraint before completing the table creation in order to ensure the check constraint on the table level.

Syntax

Following is the syntax to specify the check constraint on the table level

CREATE TABLE table_name(
   column1 datatype(size),
   column2 datatype(size),
   … columnN datatype, CONSTRAINT constraint_name CHECK(column_name condition value));

Example

We are creating a table and specifying a table level check constraint on one of the columns in the following SQL query.

CREATE TABLE products(
   PID INT NOT NULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER Date NOT NULL,
   PRICE INT,
   PRIMARY KEY(PID),
   CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <= '2023-02-09'));

In the above SQL query, we have specified a check constraint on the DATE_OF_ORDER column according to the check constraint only those records where DATE_OF_ORDER is less than "2023-02-09" are alklowed in this column.

Verification

To verify the created table, we can use the following query in the SQL −

EXEC sp_help "dbo.products";

It will show all the details of the created table, including how many columns have check constraints on the table level.

+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+
| constraint_type               | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys                |
+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+
| CHECK on column DATE_OF_ORDER | Constraint_DOO  | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([DATE_OF_ORDER]<='2023-02-09')|
| PRIMARY KEY (clustered)       | PK__products    | (n/a)         | (n/a)         | (n/a)	   | (n/a)                  |                           PID |
+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+

Now, we're writing a query to insert values in the students table that have a Date_of_order is greater than ('2023-02-09').

INSERT INTO products VALUES(001, 'Nike Shoe', 'Ranchi', '2023-02-11', 2000);

The query throws an error when we try to insert the data because we are utilizing the date, whose value is greater than '2023-02-09'.

Check constraint after table creation

Suppose there arises a scenario where we need to apply the check constraint after creating the table; or on an already existing table in a database, SQL provides a way to do that. In this situation, we can use the ALTER statement to apply the check constraint to a column of a table that has already been created.

Syntax

ALTER TABLE table_name ADD CONSTRAINT ConstraintName CHECK(ColumnName condition Value);

Example

Consider that we have a customers table with an age column. To add a check constraint to the age column, we are using the query below.

ALTER TABLE customers ADD CONSTRAINT Constraint_Age CHECK (AGE >= 21);

Verification

Use this SQL query to check the newly formed or changed table −

EXEC sp_help 'dbo.customers'; 

It will display all of the table's information, including the constraint we added to the age column.

+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+
| constraint_type     | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys |
+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+
| CHECK on column AGE | Constraint_Age  | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([AGE]>=(21)))  |
+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+

Removing a check constraint

If there is a way to apply a constraint on a column, then you must also be able to delete the constraint from that column. To do that, you can use the ALTER… DROP statement.

Syntax

Following is the syntax to remove a constraint from the table −

ALTER TABLE table_name DROP CONSTRAINT constraint_set;

Example

Following example demonstrates how to drop the check constraint from the Customers table created above. We first check if any column in the Customers table has the check constraint and if it does, we can use the ALTER command to remove it.

ALTER TABLE customers drop CONSTRAINT Constraint_Age;

Verification

Here we can see that earlier we added a check constraint on the age column. However, after using the above SQL query, there is no constraint in the table, as shown in the table below −

+-------------------------+---------------------------------+---------------+----------------+-----------------+
| constraint_type         | constraint_name                 | update_action | status_enabled | constraint_keys |
+-------------------------+---------------------------------+---------------+----------------+-----------------+
| PRIMARY KEY (clustered) | PK__CUSTOMER__3214EC270E48CD10  | (n/a)         | (n/a)          |    ID           |
+-------------------------+---------------------------------+---------------+----------------+-----------------+