CHECK Constraint in SQL

Last Updated: 22nd June, 2023

Overview

Check-in SQL may be a database management system utilized to store and manage information. It is one of the foremost commonly utilized relational database management systems and is utilized in many different businesses for an assortment of purposes. It permits clients to create and manage databases and perform inquiries on the information put away within the database. Furthermore, it gives support for transactions, security, and other highlights. It is one of the foremost prevalent database systems and is utilized by numerous organizations over the world.

Check constraint on column level:

In the hospitality industry, check-in SQL can be used to determine the availability of hotel rooms. By running a query on the hotel’s database, the system can quickly determine which rooms are currently occupied and which are available. This can help the staff quickly check in new guests and avoid any confusion or double bookings.

Syntax:

CREATE TABLE table_name (
column1 datatype CONSTRAINT constraint_name CHECK (expression)
);

Explanation:

The syntax above creates a table with a column-level check constraint. The CHECK keyword enforces a constraint on a column's values. The expression in the parentheses is evaluated for each row in the table and must return true for the row to be inserted or updated. If the expression returns false, the statement will fail, and the row will not be inserted or updated.

Example

CREATE TABLE employees (
name varchar(255) CONSTRAINT age_check CHECK (age > 18)
);

This statement creates a table called "employees" with a column called "name" of data type varchar(255). The check constraint "age_check" is also added, which requires that the value of the "age" column must be greater than 18 for each row.

Check constraint on table level:

Syntax

CREATE TABLE TableName (
  Column1 datatype, 
  Column2 datatype, 
  Column3 datatype, 
  CONSTRAINT constraint_name CHECK (Column1 > 0)
);

This statement creates a table named TableName with three columns, Column1, Column2, and Column3, each with its own data type. Additionally, a check constraint is added that requires that the value of Column1 must be greater than 0. This check constraint will be enforced whenever data is added to or updated in the table.

Example

CREATE TABLE Employees (
  EmployeeID int, 
  Name varchar(255), 
  Age int, 
  CONSTRAINT AgeCheck CHECK (Age > 18)
);

This creates a table named Employees with three columns (EmployeeID, Name, and Age) and a check constraint that requires Age to have a value greater than 18. The constraint is named AgeCheck.

Check constraint after table creation:

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

This statement will add a check constraint to the specified table. The condition specified in the CHECK clause will be evaluated for all values in the table. If the condition evaluates to false for any data value, the constraint is violated, and the statement will be rolled back.

Example

ALTER TABLE Orders
ADD CONSTRAINT chk_order_date CHECK (order_date > '2020-01-01');

This statement adds a check constraint to the Orders table, which checks that the order_date is greater than '2020-01-01'. If any existing order in the table has an order_date that is not greater than '2020-01-01', then the statement will be rolled back, and the constraint will not be applied.

Remove a check constraint

Syntax

ALTER TABLE table_name 
DROP CONSTRAINT check_constraint_name;

The above ALTER TABLE statement removes a check constraint from an existing table. It requires the table's name and the check constraint's name to be dropped. Once executed, the check constraint will no longer be enforced on the table.

Example

ALTER TABLE bookings
DROP CONSTRAINT ck_booking_max_guests;

This ALTER TABLE statement will remove the check constraint named “ck_booking_max_guests” from the “bookings” table. After executing this statement, the check constraint will no longer be enforced, and bookings with more than 6 guests will be allowed.

Conclusion

The staff at the hospitality business used a check-in SQL query to determine the availability of hotel rooms quickly. This query allowed them to easily identify which rooms were occupied and which were available, allowing them to quickly check in new guests and avoid any confusion or double bookings. This efficient system provided a streamlined check-in process, ensuring guests were taken care of in no time.

Key takeaways

1. CHECK is a constraint that allows you to ensure a value meets a certain condition before it is inserted into a table.
2. The condition can be defined as a Boolean expression, and it will be evaluated for each added row.
3. CHECK constraints are a powerful tool for maintaining data integrity and ensuring data consistency in your databases.
4. It is important to carefully consider the conditions you define for your CHECK constraints, as they can limit the data inserted into your tables.

QUIZ

1.What type of operation is used to add a new record to a table?

1. INSERT 
2. SELECT 
3. UPDATE
4. DELETE

Answer: a. INSERT

2. Which of the following is used to specify the columns when using a SELECT statement?

1. JOIN 
2. WHERE 
3. GROUP BY 
4. COLUMN

Answer: d. COLUMN

3. How do you specify a condition in a WHERE clause?

1. By using comparison operators 
2. By using logical operators 
3. By using arithmetic operators 
4. By using SELECT statements

Answer: a. By using comparison operators

4. Which statements are used to delete a record from a table?

1. SELECT 
2. INSERT 
3. DELETE 
4. UPDATE

Answer: c. DELETE