SQL CHECK

SQL CHECK Constraint Explained

SQL CHECK constraint is a crucial feature in SQL that allows you to specify a condition on each row in a table. The purpose of this constraint is to ensure that all values in a given field meet a defined condition before they are permitted to be entered into the database. This enhances data integrity by preventing invalid data entry, which could later lead to inconsistencies.When using the CHECK constraint, it can be applied to single or multiple columns and can enforce a wide range of rules. Conditions can include ranges of values, specific formats, or even references to other columns. For instance, ensuring a numerical value is non-negative or that a particular string follows a specified pattern are both common uses. Here are some key points about the SQL CHECK constraint:

• It can restrict data for any columns in a table.
• It can be used to enforce domain integrity.
• It works at the row level, meaning it evaluates whether the data fits the condition before the insert or update actions.

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Age INT CHECK (Age >= 18));

CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), StockLevel INT CHECK (StockLevel >= 0));

SQL CHECK Constraint Syntax

Understanding SQL CHECK for Students

SQL CHECK constraint is a powerful mechanism for ensuring that only valid data is entered into your database. This constraint allows you to define specific conditions that data must meet. If the data doesn't meet these conditions, it cannot be entered or updated in the database.For example, a CHECK constraint can be used to ensure that a certain column only contains values within a certain range. This is critical in maintaining data integrity throughout the database.Some common use cases of the CHECK constraint include:

• Ensuring age is above 18 in a table storing user demographics
• Restricting salary to be greater than zero in an employee database
• Ensuring that a product's quantity in stock cannot be negative

CREATE TABLE StudentGrades ( StudentID INT PRIMARY KEY, StudentName VARCHAR(50), Grade INT CHECK (Grade >= 0 AND Grade <= 100));

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Department VARCHAR(50), Salary DECIMAL(10, 2), CHECK (Salary > 0 AND (Department = 'Sales' OR Department = 'HR')));

SQL CHECK Constraint Example

SQL CHECK Exercises for Beginners

SQL CHECK constraints play a vital role in maintaining data integrity by imposing conditions on column values. Constructing practical examples will help solidify your understanding of how these constraints function in SQL.Consider the following simple example of a table that tracks product information for an online store. This table includes a CHECK constraint to ensure that the price of each product is never negative:

CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Price DECIMAL(10, 2) CHECK (Price >= 0));

CREATE TABLE Students ( StudentID INT PRIMARY KEY, StudentName VARCHAR(100), Age INT CHECK (Age >= 18));

CREATE TABLE BankAccounts ( AccountID INT PRIMARY KEY, AccountHolder VARCHAR(100), Balance DECIMAL(10, 2) CHECK (Balance >= 0));

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, JobLevel VARCHAR(20), Salary DECIMAL(10, 2), CHECK (Salary > 0 AND (JobLevel = 'Manager' OR JobLevel = 'Staff')));