[MySQL] Day 7. CHECK Constraint

In this post, we will learn about CHECK constraint used in MySQL.

W3Schools.com

 

CHECK constraint is used to limit the values that can be used in a column. 

 

Let's start from our employees table that we have created before.

SELECT * FROM employees;

employees

 

In many countries, they have their own minimum wage for workers. 

Every employee in the region must be paid for at least minimum wage. 

We can check if a new employee is paid at least more than the minimum wage using CHECK constraint. 

 

Say we were creating the table, and we want to add CHECK constraint for minimum wage, $10.00 for an hour. 

CREATE TABLE employees(
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hourly_pay DECIMAL(4, 2),
    hire_date DATE,
    CONSTRAINT chk_min_wage CHECK (hourly_pay >= 10.00)
);

 

The syntax itself is like this:

-- CONSTRAINT constraint_name CHECK (condition)

 

So chk_min_wage is the name of the CHECK constraint added on hourly_pay column that checks if hourly_pay data is greater than or equal to $10.00 per hour. 

 

Now since we already have employees table created, we want to modify the table to add the CHECK constraint. 

The syntax for adding CHECK constraint is the same with that of UNIQUE constraint. 

[MySQL] Day 6. UNIQUE & Not Null Constraint

ALTER TABLE employees
ADD CONSTRAINT chk_min_wage CHECK (hourly_wage >= 10.00);

 

Now, after you succeed adding CHECK constraint, you can find it in the Table Inspector > DDL (Data Definition Language)

 

 

Now, let's try adding a new employee with his hourly_pay data less than $10.00. 

INSERT INTO employees
VALUES (6, "Sheldon", "Plankton", 5.00, "2023-01-07");
-- Error Code: 3819. Check constraint 'chk_min_wage' is violated.

It shows an error that the constraint chk_min_wage is violated as expected. 

 

 

Lastly, let's learn how to remove a constraint.

ALTER TABLE employees
DROP CHECK chk_min_wage;

 

Going back to Table Inspector > DDL 

We can check that the chk_min_wage constraint has disappeared now!