In this part of the MySQL tutorial, we will work with constraints.
Constraints are placed on columns or tables. They limit the data that can be inserted into tables.
We have the following constraints:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Other databases also have the CHECK constraint, which places a condition on a valid data. MySQL parses this constraint, but it is not enforced.
NOT NULL constraint
A column with a
NOT NULL constraint, cannot have NULL values.
mysql> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL, -> FirstName TEXT NOT NULL, City VARCHAR(55)); Query OK, 0 rows affected (0.07 sec)
We create two columns with
NOT NULL constraints.
mysql> INSERT INTO People VALUES(1, 'Hanks', 'Robert', 'New York'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO People VALUES(1, NULL, 'Marianne', 'Chicago'); ERROR 1048 (23000): Column 'LastName' cannot be null
SELECT statement is executed OK, the second
one fails. The SQL error says, the
LastName column may not be null.
UNIQUE constraint ensures that all data are unique in
mysql> CREATE TABLE Brands(Id INTEGER, BrandName VARCHAR(30) UNIQUE); Query OK, 0 rows affected (0.08 sec)
Here we create a table
BrandName column is
set to be
UNIQUE. There cannot be two brands with the same name.
mysql> INSERT INTO Brands VALUES(1, 'Coca Cola'); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Brands VALUES(2, 'Pepsi'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Brands VALUES(3, 'Pepsi'); ERROR 1062 (23000): Duplicate entry 'Pepsi' for key 'BrandName'
We get an SQL error Duplicate entry 'Pepsi' for key 'BrandName'. There can only be one Pepsi brand.
Note that a
PRIMARY KEY constraint automatically
UNIQUE constraint defined on it.
PRIMARY KEY constraint uniquely identifies each record
in a database table. It is a special case of unique keys. Primary keys cannot
NULL, unique keys can be. There can be more
columns, but only one primary key in a table. Primary keys are important
when designing the database tables. Primary keys are unique ids. We use them
to refer to table rows. Primary keys become foreign keys in other tables,
when creating relations among tables.
mysql> DROP TABLE Brands; mysql> CREATE TABLE Brands(Id INTEGER PRIMARY KEY, BrandName VARCHAR(30) UNIQUE);
The Id column of the
Brands table becomes a primary key.
mysql> DESCRIBE Brands; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | Id | int(11) | NO | PRI | NULL | | | BrandName | varchar(30) | YES | UNI | NULL | | +-----------+-------------+------+-----+---------+-------+
DESCRIBE statement shows information about the
columns in a table. We can see that the Id column has a
defined and the
set. The primary key is used to uniquely identify the row in a table, when dealing
with a specific table. The unique key enforces that all data
in a column are not duplicate.
FOREIGN KEY in one table points to a
PRIMARY KEY in another table.
It is a referential constraint between two tables. The foreign key identifies a
column or a set of columns in one (referencing) table that refers to a column or set
of columns in another (referenced) table.
We will be demonstrating this constraint on two tables:
mysql> CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name VARCHAR(70)) -> type=InnoDB;
Here we create the Authors table. In MySQL, the referencing and the referenced tables must be of InnoDB or BDB storage engines. In the MyISAM storage engines the foreign keys are parsed, but they are not enforced.
mysql> CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title VARCHAR(50), -> AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)) -> type=InnoDB;
We create the
Books table. Here we have an
column name, which acts as a foreign key. It references to the primary
key of the
What would foreign key enforcement mean in our example? We could not
insert a row into the
Books table with an
which is not present in
ENUM is a string object with a value chosen from a list of
permitted values. They are enumerated explicitly in the column specification
at table creation time.
mysql> CREATE TABLE Shops(Id INTEGER, Name VARCHAR(55), -> Quality ENUM('High', 'Average', 'Low'));
We have a
Shops table. The table has an
Quality columns defined. The
Quality column is an
permits to have one of three specified values:
mysql> INSERT INTO Shops VALUES(1, 'Boneys', 'High'); mysql> INSERT INTO Shops VALUES(2, 'AC River', 'Average'); mysql> INSERT INTO Shops VALUES(3, 'AT 34', '**'); mysql> SELECT * FROM Shops; +------+----------+---------+ | Id | Name | Quality | +------+----------+---------+ | 1 | Boneys | High | | 2 | AC River | Average | | 3 | AT 34 | | +------+----------+---------+
In the first two statements, we have inserted two rows. In the
third case, the value is not available in the
In this case an empty string is inserted.
SET can have zero or more values.
Each of the values must be chosen from a list of permitted values.
mysql> CREATE TABLE Students(Id INTEGER, Name VARCHAR(55), -> Certificates SET('A1', 'A2', 'B1', 'C1'));
We have a
Students table. In this table, we have a Certificates
column. Each student can have 0, 1 or more of these certificates.
This is different from the
ENUM constraint, where
you can have only one distinct value from the list of permitted
mysql> INSERT INTO Students VALUES(1, 'Paul', 'A1,B1'); mysql> INSERT INTO Students VALUES(2, 'Jane', 'A1,B1,A2'); mysql> INSERT INTO Students VALUES(3, 'Mark', 'A1,A2,D1,D2'); mysql> SELECT * FROM Students; +------+------+--------------+ | Id | Name | Certificates | +------+------+--------------+ | 1 | Paul | A1,B1 | | 2 | Jane | A1,A2,B1 | | 3 | Mark | A1,A2 | +------+------+--------------+
Paul has two certificates, Jane has three, Mark has four, but only two of them are recognised, so only the first two were written to the table. The certificates are separated by commas. No spaces are allowed.
In this part of the MySQL tutorial, we have covered constraints supported by MySQL.