You are currently viewing SQL 1.49 SQL CHECK

SQL 1.49 SQL CHECK

Introduction to SQL CHECK Constraints

SQL CHECK constraints are used to enforce domain integrity by limiting the values that can be entered into a column. They ensure that only valid data is inserted into the database tables.

Syntax of SQL CHECK Constraint:

CREATE TABLE table_name (
    column1 datatype CHECK (condition),
    column2 datatype CHECK (condition),
    ...
);

Example:

Suppose we have a table named Employees with columns EmployeeID, FirstName, LastName, and Age. We want to ensure that the age of employees must be between 18 and 65.

Step 1: Creating the Table with CHECK Constraint

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

In this example, CHECK (Age >= 18 AND Age <= 65) ensures that the Age column can only contain values between 18 and 65.

Step 2: Inserting Valid Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age) 
VALUES (1, 'John', 'Doe', 30);

This query will successfully insert the record because the age (30) falls within the specified range.

Step 3: Inserting Invalid Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age) 
VALUES (2, 'Jane', 'Smith', 17);

This query will fail because the age (17) violates the CHECK constraint.

Additional Examples:

Let’s consider a few more scenarios to understand SQL CHECK constraints better:

Example 1: Ensuring Gender is either ‘Male’ or ‘Female’

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Gender VARCHAR(10) CHECK (Gender IN ('Male', 'Female'))
);

Example 2: Ensuring Price is Positive

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(50),
    Price DECIMAL(10,2) CHECK (Price > 0)
);

Summary:

SQL CHECK constraints are vital for maintaining data integrity by restricting the values that can be inserted into a column. They ensure that only valid data is stored in the database. By following the syntax and examples provided, you can effectively utilize CHECK constraints in your SQL database schema.

Leave a Reply