Prev Next
- CHECK is a constraint in SQL which is used to validate the value of a field while inserting into a table, whether it is within the specified range/condition.
- If the value to be inserted is not within the given range or condition, then, it will not be inserted into a table. Syntax for SQL DEFAULT constraint is given below.
SQL Syntax for CHECK constraint:
| SQL Syntax for CHECK constraint | CREATE TABLE table_name (column_name1 INTEGER AUTOINCREMENT, column_name2 data_type(size), column_name3 data_type(size) DEFAULT value, CHECK column_name1 condition, etc…); |
Example1 for how to use SQL CHECK constraint on a single column:
- Please execute below query in SQL to create a table with CHECK constraint.
| CREATE TABLE student (Student_ID int, Student_name varchar(255), City varchar(255) DEFAULT ‘Chennai’, Marks int, CHECK (Student_ID > 0)); |
- Now, a new table called “student” is created. The CHECK constraint validates whether the 1st column ‘Student_ID’ value is greater than 0. If it is greater than 0, then the record will be inserted in the table. Otherwise, the record will not be inserted.
- Now, please insert some records using insert SQL as below and see how data are validated using CHECK constraint before data are inserted into the table.
SQL queries:
| INSERT INTO student (Student_ID, Student_name, City, Marks) VALUES (0, ‘Raju’, ‘Chennai’, 80);INSERT INTO student (Student_ID, Student_name, City, Marks) VALUES (1, ‘Mani, ‘Chennai’, 85);INSERT INTO student (Student_ID, Student_name, City, Marks) VALUES (2, ‘Thiyagarajan’, ‘Vizag’, 70); INSERT INTO student (Student_ID, Student_name, City, Marks) |
SQL queries output:
Note:
- As we have a CHECK constraint (Student_ID > 0), it allows the records having Student_ID > 0 only.
- Other records are dropped without inserting as they fail this validation. So, in above 4 insert queries, only 2 queries are allowed to insert the records.
Example2 for how to use SQL CHECK constraint on multiple columns:
If you want to use CHECK constraint on more than one column, you can use below query statement. This statement will validate both Student_ID and Marks which should be greater than 0. You can use as per your requirement in real time.
| CREATE TABLE student (Student_ID int, Student_name varchar(255), City varchar(255) DEFAULT ‘Chennai’, Marks int, CONSTRAINT more_Checks CHECK (Student_ID > 0 AND Marks > 0)); |
How to ADD a CHECK constraint in an existing table?
- To ADD a CHECK constraint on an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name ADD CHECK condition; |
Example:
| ALTER table Student ADD CHECK (Student_ID >=0) |
How to DROP a CHECK constraint?
- To DROP a CHECK constraint is an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name DROP CHECK column_name; |
Example:
| ALTER table Student DROP CHECK Student_ID; |