How to use MySQL constraints?

MySQL constraints are rules applied to columns defined in a database table to restrict the operations of insertions, updates, or deletions. They ensure the integrity and consistency of data.

In MySQL, constraints can be defined using the following keywords:

1. PRIMARY KEY: Defines a primary key constraint on a column to uniquely identify each row in a table. A table can only have one primary key constraint, and the primary key values cannot be NULL.

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

1. A FOREIGN KEY is used to establish a relationship between tables by defining a constraint on a column. The value of the foreign key must either match a primary key value in the referenced table or be NULL if allowed. The referenced table must exist.

CREATE TABLE table_name1 (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
    FOREIGN KEY (column2) REFERENCES table_name2(column1)
);

1. UNIQUE: Defining a unique constraint on a column to ensure that the values in the column are unique and cannot be repeated.

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

1. NOT NULL: A constraint defined on a column to ensure that the values in the column cannot be NULL.

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

1. CHECK: Define a check constraint on a column to restrict the values in the column to meet specific conditions.

CREATE TABLE table_name (
    column1 datatype CHECK (condition),
    column2 datatype,
    ...
);

1. DEFAULT: Default constraint is defined on a column to specify that if a value is not provided for that column when inserting a new row, then the default value should be used.

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype,
    ...
);

Constraints can be defined when creating a table or added, modified, or deleted on an existing table using the ALTER TABLE statement.