MySQL Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

• used to specify rules for data in a table.
• used to limit the type of data that can go into a table.

This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

Syntax:

  CREATE TABLE table_name (
        column1 datatype constraint,
        column2 datatype constraint,
        column3 datatype constraint,
        ....
    );

NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value. By default, MySQL allows NULL values in columns, but with the NOT NULL constraint, you can prevent this.

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2)
);

• In this example, the id and name columns cannot have NULL values, but salary can.

ALTER TABLE Persons
MODIFY Age INT NOT NULL;

• In this example, the Age column cannot have NULL values anymore.

UNIQUE

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are different. No two rows can have the same value for a column (or combination of columns).

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100) UNIQUE
);

UNIQUE Constraint on ALTER TABLE

ALTER TABLE Persons
    ADD UNIQUE (ID);

DROP a UNIQUE Constraint

ALTER TABLE Persons
    DROP INDEX UC_Person;

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table. A primary key must contain unique values and cannot contain NULL values. Each table can only have one primary key.

CREATE TABLE students (
    student_id INT NOT NULL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

FOREIGN KEY

The FOREIGN KEY constraint is used to link two tables together. It ensures that the value in a column matches a value in another table’s primary key or unique key column. It enforces referential integrity between the two tables.

CREATE TABLE orders (
    order_id INT NOT NULL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

• In this example, the customer_id in the orders table must match an id value in the customers table.

CHECK

The CHECK constraint is used to ensure that values in a column satisfy a specific condition. This was introduced in MySQL 8.0.16 and is not supported in earlier versions.

CREATE TABLE orders (
    order_id INT NOT NULL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

• In this example, the price and stock columns must contain non-negative values.

DEFAULT

The DEFAULT constraint provides a default value for a column when no value is specified during the INSERT operation.

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    join_date DATE DEFAULT CURRENT_DATE
);

• In this example, if no join_date is provided during insertion, the current date will be used as the default.

CREATE INDEX

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX index_name
    ON table_name (column1, column2, ...);

Create UNIQUE Index Syntax

 CREATE UNIQUE INDEX index_name
    ON table_name (column1, column2, ...);

AUTO INCREMENT

AUTO_INCREMENT allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

  CREATE TABLE Persons (
        Personid int NOT NULL AUTO_INCREMENT,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Age int,
        PRIMARY KEY (Personid)
    );