meta data for this page

Constraints in MySQL

Introduction

One of the main advantages of storing data in relational databases is the easy control of data integrity. In MySQL you can specify a number of constraints that help you generating and enforcing data integrity. In essence, constraints are nothing else but rules, which can among others

  • enforce that a column has to be filled with data (e.g. an column specifying the identifier of an entry must not be empty) → See NOT NULL in table 1
  • specify what values can be stored in columns (e.g. you don't want to accept a character string in a column that should inform about the starting position of a gene in a genome)1)
  • limit the amount of data that should be entered into a column
  • make sure that not the same value is inserted twice into a table → See UNIQUE in table 1

Once you submit an INSERT command to MySQL, the management system will first check whether your command satisfies all constraints that have been specified for your database. If this is the case, then the insert statement is performed, otherwise the management system throws an error and refuses to insert the data. See the MySQL manual for further details.

In general, MySQL constraints can be classified into two types - column level and table level. The column level constraints can apply only to one column in a table, whereas table level constraints are applied to the entire table.

MySQL constraints - an overview

The table below provides an overview of the different types of constraints.

MySQL CONSTRAINT is declared at the time of creating a table.

CONSTRAINT DESCRIPTION
NOT NULL In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.
FOREIGN KEY A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT In a MySQL table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

Table 1: An overview of typical constraints available in MySQL together with a brief description. See the CREATE TABLE or ALTER TABLE syntax for details how invoke these constraints.

1)
in fact, specifying the data type for a column is already a constraint