SQL CHECK Constraint

SQL CHECK Constraint

The SQL CHECK constraint is used to limit the values that can be placed in a column. This command is essential for enforcing data integrity and ensuring that the data in a database meets specific criteria.

---

Syntax

-- To create a CHECK constraint when creating a table
CREATE TABLE table_name (
    column1 datatype CHECK (condition),
    column2 datatype,
    ...
);

-- To add a CHECK constraint to an existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

• CREATE TABLE: This is the SQL keyword used to create a new table.
• ALTER TABLE: This is the SQL keyword used to modify an existing table.
• CHECK: This is the SQL keyword used to define a CHECK constraint.
• column1, column2, ...: These specify the names of the columns in the table.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
• condition: This specifies the condition that the data must meet to be valid.
• table_name: This specifies the name of the table to create or modify.
• constraint_name: This specifies the name of the CHECK constraint.

---

Example

Let's go through a complete example that includes creating a database, creating a table with a CHECK constraint, and inserting data into the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table with a CHECK Constraint

In this step, we create a table named employees within the previously created database, defining a CHECK constraint on the salary column to ensure it is greater than zero.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

Here, we define the employees table with columns for id, first_name, last_name, email, hire_date, and salary. The salary column has a CHECK constraint to ensure it is greater than zero.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01', 50000.00);
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01', 60000.00);
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01', 70000.00);
INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01', 0.00);

The first three insertions will be successful because the salary values are greater than zero. The fourth insertion will fail because the salary value is not greater than zero, violating the CHECK constraint.

Step 4: Adding a CHECK Constraint to an Existing Table

This step involves adding a CHECK constraint to an existing table named departments to ensure that the budget column is greater than 1000.

ALTER TABLE departments
ADD CONSTRAINT chk_budget CHECK (budget > 1000);

This command adds a CHECK constraint to the budget column of the departments table, ensuring that the budget is always greater than 1000.