MySQL - CONSTRAINTS

The MySQL CONSTRAINTS are used to specify rules on data columns of a table. These constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with ALTER TABLE statement.

Constraints are used to limit the type of data that can be stored in a table and consequently ensures accuracy and reliability of the data in the database.

Constraints can be applied on column level as well as table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.

MySQL Create Constraints

The syntax for specifying MySQL CONSTRAINTS, when the table is created, are given below:

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

MySQL Constraints

The below mentioned constraints are commonly used in MySQL:

• NOT NULL - Ensures that a column cannot have a NULL value.
• UNIQUE - Ensures that all values in a column are different.
• PRIMARY KEY - Uniquely identifies each row/record in a column of a table. A combination of a NOT NULL and UNIQUE constraints.
• FOREIGN KEY - Uniquely identifies a row/record in a column of another table.
• CHECK - Ensures that all values in a column satisfies certain conditions.
• DEFAULT - Sets a default value for a column when none is specified.
• INDEX - Used to create and retrieve data from the database very quickly.