SQL CHECK Constraint 🎯

Welcome back to CodeYourCraft! Today, we're diving into one of the most powerful tools in SQL - the CHECK Constraint. This feature helps ensure data integrity in your databases, making your applications more robust and reliable.

What is a CHECK Constraint? 📝

A CHECK Constraint is a rule that you can apply to a table column to restrict the values that can be inserted or updated in that column. It's a way to enforce business rules directly in the database, which can save you from having to write additional validation code in your application.

Why Use CHECK Constraints? 💡

Enforces business rules: Prevents invalid data from being stored in the database.
Reduces application code: Less code to write in your application because the validation is handled at the database level.
Improves data consistency: Helps maintain the accuracy and reliability of the data.

How to Create a CHECK Constraint? 🎯

Here's a simple example of creating a CHECK Constraint in SQL. We'll create a table Employees and add a CHECK Constraint to ensure that the Salary column is always greater than or equal to 1000.

sql

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10, 2) CHECK (Salary >= 1000)
);

In this example, we've created a table Employees with three columns: ID, Name, and Salary. The CHECK Constraint is applied to the Salary column, ensuring that any value inserted or updated for this column must be greater than or equal to 1000.

Practical Example 💡

Let's add a real-world example to better illustrate the use of CHECK Constraints. Suppose we have a table Orders for an e-commerce store, and we want to ensure that the OrderQuantity for each order is always greater than or equal to 1.

sql

CREATE TABLE Orders (
    ID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderQuantity INT CHECK (OrderQuantity >= 1),
    OrderDate DATE
);

With this CHECK Constraint in place, any attempt to insert or update an order with a OrderQuantity less than 1 will result in an error.

Advanced Usage 🎯

CHECK Constraints can also be used with complex expressions. Here's an example where we're ensuring that the Salary column in the Employees table is either greater than or equal to 1000 or the JobTitle is "CEO".

sql

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10, 2) CHECK (Salary >= 1000 OR JobTitle = 'CEO'),
    JobTitle VARCHAR(20)
);

In this example, if the Salary is less than 1000, the JobTitle must be "CEO" for the record to be valid.

SQL CHECK Constraint 🎯

SQL CHECK Constraint 🎯

What is a CHECK Constraint? 📝

Why Use CHECK Constraints? 💡

How to Create a CHECK Constraint? 🎯

Practical Example 💡

Advanced Usage 🎯

Quiz Time 🎯