MySQL Constraints Guide

In MySQL, constraints are used to define rules that enforce data integrity within a table. They help ensure that the data stored in the table meets specific criteria and prevents the insertion of invalid or inconsistent data. Here are some examples of commonly used constraints in MySQL:

PRIMARY KEY Constraint:

The PRIMARY KEY constraint is used to define a column or a combination of columns that uniquely identify each row in the table. It enforces the uniqueness of the values and automatically creates an index for faster data retrieval. Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

UNIQUE Constraint:

The UNIQUE constraint ensures that the values in the specified column or combination of columns are unique and do not have any duplicates. Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50)
);

NOT NULL Constraint:

The NOT NULL constraint ensures that a column does not accept NULL values, meaning it must have a value during data insertion. Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    total_amount DECIMAL(10, 2)
);

DEFAULT Constraint:

The DEFAULT constraint sets a default value for a column when no value is explicitly provided during data insertion. Example:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT DEFAULT 0
);

FOREIGN KEY Constraint:

The FOREIGN KEY constraint establishes a relationship between two tables based on a column. It ensures that the values in the specified column exist as primary key values in another table. Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

MySQL CHECK Constraint

MySQL supports the CHECK constraint starting from version 8.0.23. The CHECK constraint is used to enforce a condition on a column or a set of columns, ensuring that only valid data is inserted into the table.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    CHECK (price >= 0 AND quantity >= 0)
);

In this example, the customer_id column in the “orders” table references the customer_id column in the “customers” table, creating a relationship between the two.

These are some of the commonly used constraints in MySQL. By using constraints effectively, you can maintain data integrity and improve the overall quality of your database.

MySQL – Constraints