MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL CHECK: Check Constraints

The CHECK constraint in MySQL is used to limit the values that can be placed in a column. The constraint specifies a condition that must be true for the data in the column.

Prerequisites:

A MySQL server up and running (version 8.0.16 or newer because earlier versions of MySQL do not enforce CHECK constraints).
Access to a MySQL user account with privileges to create and modify tables

Tutorial:

Connect to the MySQL server:

To start the mysql command-line client, open a terminal or command prompt, and enter:

mysql -u [username] -p

Replace [username] with your MySQL username and enter your password when prompted.

Select a database:

Select the database where you want to create your table:

USE [database_name];

Replace [database_name] with the name of your database.

Create a table with a CHECK constraint:

Suppose you want to create a products table with a price column, and you want to ensure that the price is always greater than 0. You can use a CHECK constraint:

CREATE TABLE products (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    CHECK (price > 0),
    PRIMARY KEY (id)
);

In this example, the CHECK constraint ensures that you cannot insert a product with a price that is less than or equal to 0.

Insert data into the table:

When you insert data into the table, MySQL will check the price value against the CHECK constraint:

INSERT INTO products (name, price) VALUES ('Product 1', 99.99);
-- This will succeed because 99.99 is greater than 0

INSERT INTO products (name, price) VALUES ('Product 2', -10);
-- This will fail because -10 is not greater than 0

Add a CHECK constraint to an existing table:

You can also add a CHECK constraint to an existing table using the ALTER TABLE statement:

ALTER TABLE products
ADD CHECK (price <= 1000);

After running this statement, you cannot insert a product with a price greater than 1000.

Exit the MySQL command-line client:

EXIT;

By using the CHECK constraint in MySQL, you can enforce specific rules on your data, ensuring that only valid data is inserted into your tables.

How to use CHECK constraints in MySQL:

CHECK constraints are used to enforce specific conditions on column values.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_age INT CHECK (emp_age >= 18),
  emp_salary DECIMAL(10, 2) CHECK (emp_salary > 0)
);

Creating tables with CHECK constraints in MySQL:
- Define CHECK constraints during table creation to enforce conditions.
```
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  stock_quantity INT CHECK (stock_quantity >= 0)
);
```

Check constraints vs triggers in MySQL:

CHECK constraints are simpler and more efficient for basic validation, while triggers offer more complex logic.

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.emp_salary <= 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary must be greater than 0';
  END IF;
END;

Applying conditions with CHECK in MySQL:

Apply conditions to restrict acceptable values for a column.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_status VARCHAR(20) CHECK (order_status IN ('Pending', 'Processing', 'Shipped'))
);

MySQL CHECK constraint examples:

Examples of using CHECK constraints in different scenarios.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  loyalty_points INT CHECK (loyalty_points >= 0)
);

CHECK constraints and data integrity in MySQL:

CHECK constraints enhance data integrity by preventing invalid values.

CREATE TABLE bookings (
  booking_id INT PRIMARY KEY,
  booking_status VARCHAR(20) CHECK (booking_status IN ('Confirmed', 'Cancelled'))
);

CHECK constraints for numeric values in MySQL:

Define CHECK constraints for numeric columns.

CREATE TABLE grades (
  student_id INT PRIMARY KEY,
  grade INT CHECK (grade BETWEEN 0 AND 100)
);

Adding CHECK constraints to existing tables in MySQL:
- Add CHECK constraints to existing tables using ALTER TABLE.
```
ALTER TABLE your_table
ADD CHECK (column_name > value);
```

Combining CHECK constraints with other constraints in MySQL:

Combine CHECK constraints with other constraints for comprehensive validation.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  stock_quantity INT CHECK (stock_quantity >= 0),
  CONSTRAINT positive_price CHECK (product_price > 0)
);

MySQL CHECK constraints for string values:

Apply CHECK constraints to string columns.

CREATE TABLE usernames (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) CHECK (CHAR_LENGTH(username) >= 5)
);

Enforcing business rules with CHECK constraints in MySQL:

Utilize CHECK constraints to enforce specific business rules.

CREATE TABLE appointments (
  appointment_id INT PRIMARY KEY,
  appointment_date DATE CHECK (appointment_date >= CURDATE())
);

CHECK constraints and NULL values in MySQL:

CHECK constraints can be applied to both NULL and non-NULL values.

CREATE TABLE discounts (
  discount_id INT PRIMARY KEY,
  discount_percentage INT CHECK (discount_percentage BETWEEN 0 AND 100) DEFAULT 0
);