In this tutorial, we'll demonstrate how to write SQL scripts to create the 7 types of constraints in MySQL.

1. Data type constraint
2. Nullability constraint
3. Default constraint
4. Primary key constraint
5. Foreign key constraint
6. Unique constraint
7. Check constraint

Here is the script used to create Products table. This script has incorporated all the syntax for creating the 7 types of constraints.

CREATE TABLE Products (
  ProductID int(10) unsigned NOT NULL AUTO_INCREMENT,
  ProductName varchar(40) NOT NULL DEFAULT '',
  SupplierID int(10) unsigned NOT NULL,
  CategoryID tinyint(5) unsigned NOT NULL,
  QuantityPerUnit varchar(20) NOT NULL DEFAULT '',
  UnitPrice double NOT NULL DEFAULT '0',
  UnitsInStock smallint(5) unsigned NOT NULL DEFAULT '0' CHECK(UnitsInStock >= 0),
  UnitsOnOrder smallint(5) unsigned NOT NULL DEFAULT '0',
  ReorderLevel smallint(5) unsigned NOT NULL DEFAULT '0',
  Discontinued enum('y','n') NOT NULL DEFAULT 'n',
  PRIMARY KEY (ProductID),
  UNIQUE KEY Uidx_products_product_name (ProductName),
  KEY idx_FK_products_categoryid (CategoryID),
  KEY idx_FK_products_supplierid (SupplierID),
  KEY idx_products_product_name (ProductName),
  CONSTRAINT FK_products_categoryid FOREIGN KEY (CategoryID) REFERENCES categories (CategoryID),
  CONSTRAINT FK_products_supplierid FOREIGN KEY (SupplierID) REFERENCES suppliers (SupplierID)
);

1. Data type constraint

Data type constraint defines the type of value a column should contain, including numeric, date and time, character, etc.

Products table has 10 columns with different types, as well as data length where applicable. For example ProductID is defined as int(10) unsigned.

ProductID int(10) unsigned NOT NULL AUTO_INCREMENT

2. Nullability constraint

Nullability constraint defines whether a column is allowed to store NULL values.

The NOT NULL constraint prevents the column from having a NULL value as it specifies that a NULL value is not allowed.

NULL constraint represents unknown data which is not the same as no data or empty data.

ProductName is defined as NOT NULL.

ProductName varchar(40) NOT NULL

3. Default constraint

Default constraint specifies a default value for a column if no value is supplied when adding a record to a table.

For example, when adding a new product to Products table, the default value for UnitsInStock is 0 if the insert query does not provide a specific value for UnitsInStock.

UnitsInStock smallint(5) unsigned NOT NULL DEFAULT '0'

4. Primary key constraint

A primary key is a column (or multiple columns) in a table that can be used to uniquely identify each row in the table. The primary key column cannot contain NULL values. A UNIQUE constraint is automatically generated for a primary key column.

One table can only have one primary key, which may consist of single or multiple columns. If the primary key contains multiple columns, it's called a composite primary key, and the combination of the multiple columns must contain distinct values.

The primary key can be either a sequentially incremented integer number or a natural selection of data that represents what is happening in the real world (e.g., Social Security Number).

In our Products table, primary key is defined on ProductID column, and it is a separate line on its own.

PRIMARY KEY (ProductID)

Another way to define primary key is add keywords PRIMARY KEY to the same line where ProductID's data type and nullability are defined (added after NOT NULL keywords).

ProductID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT

5. Foreign key constraint

Foreign key constraint defines how referential integrity is enforced between two tables. It uses a foreign key column in a table to link to a primary key column in another table.

The table with the foreign key can be called child table, referencing table, or foreign key table. The table with the primary key can be called parent table, referenced table, or primary key table.

Database engines use FOREIGN KEY constraint to enforce data in sync between the parent table and the child table. You can't delete a row in the parent table when the value of the primary key still exists in the foreign key column in the child table.

In the script below, foreign key column in Products table is defined on CategoryID column which links to the primary key column CategoryID in Categories table.

CONSTRAINT FK_products_categoryid FOREIGN KEY (CategoryID) REFERENCES categories (CategoryID)

6. Unique constraint

Sometimes the data in a column must be unique even though the column does not act as PRIMARY KEY of the table. For example, CategoryName column is unique in categories table, but CategoryName is not a primary key of the table. In this case, we create unique constraint which defines that the values in a column or columns must be unique, and no duplicates can be stored at any time. You can have many UNIQUE constraints per table.

In script below, UNIQUE constraint is created by defining a unique index on ProductName column in Products table and uniqueness is enforced by the index.

UNIQUE KEY Uidx_products_product_name (ProductName)

Alternatively, UNIQUE constraint can be created in the following ways.

ProductName varchar(40) NOT NULL DEFAULT '' UNIQUE

Or

CONSTRAINT uc_products_product_name UNIQUE (ProductName)

When a unique constraint is defined, a unique index is automatically created to improve query performance.

7. Check constraint

Check constraint enforces that values in a column must satisfy a specific condition. In the background, the database engine uses a validation rule to check data quality when they are entered into the column. This rule is defined by the user when designing the column in a table. Not every database engine supports check constraints.

Prior to MySQL 8.0.16, MySQL does not support CHECK constraint even though CHECK constraint syntax exists. A common way to mimic a real CHECK constraint is by creating enum data type.

As of MySQL 8.0.16, CREATE TABLE starts to support the true functionality of CHECK constraints for all storage engines.

Syntax wise, CHECK constraint can be written on the same line as defining data type for a column or can be written as a separate line on its own.

In code below, we created a CHECK constraint on a separate line and named it as chk1_non_negative.

CREATE TABLE Stocks
(   
   UnitsInStock int unsigned NOT NULL,   
   CONSTRAINT chk1_non_negative CHECK(UnitsInStock >= 0)
);

In code below, CHECK constraint is written on the same line as defining data type for a column.

CREATE TABLE Stocks2
(
   UnitsInStock int(10) unsigned NOT NULL DEFAULT '0' CHECK(UnitsInStock >= 0),
);

Please note, in the script above, as we didn't specify a name for the CHECK constraint, MySQL automatically generates a name for it.

If you run query below, you can see the name of the auto generated CHECK constraint is stocks2_chk_1.

SHOW CREATE TABLE stocks2;

To test our CHECK constraint, insert a negative integer to UnitsInStock, MySQL generates error message as shown below.

"Error Code: 1264. Out of range value for column 'UnitsInStock' at row 1 0.000 sec".

In MySQL version prior to MySQL 8.0.16, no error is genereated as the CHECK constraint is only symbolic, so it is simply parsed and then ignored.

Happy Coding!

Other tutorials in this category

1. Create MySQL table by using CREATE TABLE statement

2. Create MySQL table by using another table

3. Create MySQL temporary table

4. How to disable (or enable) Safe Updates in MySQL

Back to Tutorial Index Page