SQL Check

Introduction to SQL Check

SQL Check is defined as a condition that can use the CHECK Constraint to check the data value being entered into a record. If the test returns false, the record violates the constraint and is not inserted into the table. The CHECK constraint is formed by using the keyword “CHECK” followed by a Boolean expression enclosed in brackets.

Key Takeaways

• One can implement a check constraint at both the table and column levels. There could be more than one check constraint in a single table, however, check constraints will only be assigned to a single column.
• The CHECK constraint always upholds the column’s data integrity. Logic statements that yield TRUE or FALSE are employed with the CHECK constraint.
• Similar to FOREIGN KEY constraints, CHECK constraints regulate the values that are entered into a column. The distinction is in the way the suitable values are determined: CHECK constraints use a logical statement to find the values, whereas FOREIGN KEY constraints get their range of attribute values from some other table.

Overview of SQL Check

When the Boolean expression returns true or NULL, the CHECK is met. The majority of Boolean expressions result is NULL. It won’t stop null values in the constrained column when one of the operands is NULL. The NOT NULL constraint is used to ensure that the column doesn’t contain any NULL values. SQL Check increases the informational value and analysis ease of the data stored. And facilitates the extraction of insights from the data. Constraints enable us to keep an eye on our data and preserve it in our database so that it is usable for a long time.

SQL Check Constraints

It is possible to indicate that a value in a column or collection of columns must fulfill a Boolean expression using a CHECK constraint, which is an integrity constraint in SQL.

On the entire table or a particular column, we can define a CHECK constraint. When a single column is specified as the CHECK constraint, only that column’s values are checked. A CHECK constraint on a table, meanwhile, restricts the values of a field according to the values of other columns within the same row.

How to Use SQL Check?

A SQL constraint called CHECK permits database clients to insert only those items that satisfy the given condition. Each column that is defined as a CHECK constraint only accepts TRUE values. Any logical Boolean that gives True or False based on proper logical operators can be used to construct a check constraint. As an instance, the Boolean expression netpay stipulates that the value in the netpay column must be greater than 3000 and lower than 4000.

Multiple columns may be used in a check constraint’s Boolean expression. There might be several check constraints on a new column.

SQL Check Creates a Table Statement

For instance, the procedure that follows adds four columns to a new table called Researcher. Here, we’ve added a CHECK with the pro_ id field to ensure that there are pro_id from 2 to 10.

Code:

CREATE TABLE researcher
( pro_id INT NOT NULL,
rese_name VARCHAR(50) NOT NULL,
l_name VARCHAR(50),
Pay int,
CONSTRAINT check_pro_id
CHECK (pro_id BETWEEN 2 and 10)
);

Output:

Inserting a Value:

Code:

insert into researcher values(2,'andrew','jack',1200);
insert into researcher values(3,'alias','diana',1600);
insert into researcher values(4,'mercy','nisha',1400);
insert into researcher values(6,'jews','flakleny',1300);

And my table looks like this:

Now, I’m checking with the constraint.

Code:

insert into researcher values(15,'antony','sylvrster',1700);

So here when I try to insert a value’15’ it shows the check constraint violated because pro_id should take the value from 2 to 10.

Output:

SQL Check an Alter Table

The SQL ALTER TABLE statement’s check constraint creation syntax is given here:

ALTER TABLE name
ADD CONSTRAINT name
CHECK (col_name (condition));

The above syntax description is given as:

• TABLE name: The name of the table we want to change by including a check constraint.
• CONSTRAINT name: The check constraint’s name should be given.
• col_name: The table column to which the check restriction applies.
• condition: The prerequisite for the check constraint’s fulfillment.

Let’s examine another example:

Code:

CREATE TABLE Shippingcompany
(exp_id INT NOT NULL,
item_name VARCHAR(50) NOT NULL,
country_name VARCHAR(50),
price MONEY,
CONSTRAINT check_sal
CHECK (price > 2000)
);

Now let’s insert a value:

Code:

insert into Shippingcompany values(011,'Handcraft','China',2700);
insert into Shippingcompany values(012,'Toys','Canada',2800);
insert into Shippingcompany values(013,'Clothes','Norway',3800);

Select * from Shippingcompany;

Output:

Now we shall check a constraint by inserting a value of price <2000, it violates the constraint.

Code:

insert into Shippingcompany values(013,'Clothes','Norway',100);

Output:

Let’s have a look at an example of how to implement a check constraint in SQL using the ALTER TABLE statement.

To add a constraint to an existing table, then we shall include a statement like this:

Code:

ALTER TABLE Shippingcompany
ADD "CONSTRAINT" CHECK(exp_id >= 11);

Now, when I insert a value’121’ the constraint is shown as:

Code:

insert into Shippingcompany values(09,'Clothes','Norway',3800,121);

Output:

SQL Check Constraints Remove

The SQL syntax can be used to remove a check constraint from just relation in a registry:

ALTER TABLE tb_name
DROP CONSTRAINT myCheckConstraint;

alter table Shippingcompany drop check exp_id;

DELETE statements do not validate CHECK restrictions. As a result, running DELETE commands on records with particular check constraints could lead to unexpected outcomes.

FAQ

Given below are the FAQs mentioned:

Q1. What are the limits that we need to follow while giving constraint values?

Answer: The check constraint’s predicate can contain a sub-query. The range of values for an attribute is constrained by a check constraint. In most cases, the SQL CREATE TABLE instruction specifies the check constraint.

Q2. Define a Constraint.

Answer: The restrictions and requirements we place on the database columns are known as constraints. It assists us in preserving the accuracy of the data and in scanning it before we enter it into our records.

Q3. What in SQL is a Default Constraint?

Answer: In the case that no value is given while database insertion is taking place, the default constraint is used to set the value of the attributes.

Conclusion

We have covered every aspect of constraints and how they are used in databases in this article. Starting with the definition, syntax, and uses, we then moved on to the steps for each, which were each wrapped with the necessary examples.

Recommended Articles

This is a guide to SQL Check. Here we discuss the introduction, use, SQL check creates a table statement, check an alter table. You may also have a look at the following articles to learn more –

1. SQLAlchemy Example
2. SQLAlchemy Model
3. SQLAlchemy Types
4. SQLAlchemy Many to Many