MySQL Constraints

Introduction

Constraints in MySQL are used to enforce rules and restrictions on the data stored in tables. They help maintain data integrity and ensure that the data conforms to specific criteria. In this article, we will explore the commonly used constraints in MySQL with examples to illustrate their functionality.

NOT NULL Constraint

The NOT NULL constraint ensures that a column does not contain any NULL (empty) values. Here's an example of creating a table with a NOT NULL constraint:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50) NOT NULL,
    age INT
);

UNIQUE Constraint

The UNIQUE constraint ensures that the values in a column or a group of columns are unique. Here's an example of creating a table with a UNIQUE constraint:

CREATE TABLE customers (
    id INT,
    email VARCHAR(50) UNIQUE,
    phone VARCHAR(20)
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. It is a combination of the NOT NULL and UNIQUE constraints. Here's an example of creating a table with a primary key:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a relationship between two tables based on a column in each table. It ensures that values in the referencing column exist in the referenced column of another table. Here's an example of creating a table with a foreign key:

CREATE TABLE orders (
    id INT,
    customer_id INT,
    amount DECIMAL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CHECK Constraint

The CHECK constraint is used to enforce a condition on a column's values. It allows you to define a custom condition that must be satisfied for each row. Here's an example of creating a table with a CHECK constraint:

CREATE TABLE employees (
    id INT,
    age INT,
    salary DECIMAL CHECK (salary > 0)
);

Conclusion

Constraints in MySQL play a vital role in maintaining data integrity and enforcing rules on the data stored in tables. In this article, we explored the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints in MySQL. By utilizing these constraints, you can ensure that the data in your MySQL databases meets specific criteria and remains consistent, helping to prevent data corruption and inconsistencies.