Summary: in this tutorial, we will explain the PRIMARY KEY constraint concept and show you how to create the primary key constraints for a table.
Introduction to PRIMARY KEY constraint
PRIMARY KEY constraint declares a column or a combination of columns whose values uniquely identify each row in a table. This column or the combination of columns is also known as primary key of the table. If you insert or update a row that would cause duplicate primary key, SQL engines will issue an error message message. In other words, a
PRIMARY KEY constraint helps enforce the integrity of data automatically.
There are several rules that a primary key must follow:
- There is only one primary key for each table.
- If the primary key is a column, the value of this column must be unique and it must not
NULL. If the primary key consists of multiple columns, each combination of values in these columns must be unique.
- A primary key can be defined as the part of the column’s definition if it consists of one column. If a primary key consists of multiple columns, it has to be defined at the end of the CREATE TABLE statement.
- There is a restriction on the data type of the primary key column e.g., it cannot be
We often create a primary key contraint during creating the table. We can also add a
PRIMARY KEY constraint to an existing table that does not have
PRIMARY KEY constraint by using the ALTER TABLE statement. In addition, we can modify or delete an existing
PRIMARY KEY constraint of a table.
PRIMARY KEY constraint examples
Let’s take a look at some examples of using
PRIMARY KEY constraints.
PRIMARY KEY constraint that consists of 1 column example
In this example, we create a logs table to store transaction logs. The
logs table consists of two columns:
LogID is the primary key of the
logs table. We define the PRIMARY KEY constraint as a part of the column’s definition in the following
CREATE TABLE statement:
CREATE TABLE logs (
logid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
message char(255) NOT NULL
LogID column is defined as :
NOT NULL: the value in the column cannot be
NULL. In some database management systems, if you define a column as
PRIMARY KEY, it is assigned
NOT NULLattribute implicitly.
AUTO_INCREMENT: the database engine generates a sequence for the column whenever a new row is inserted into the table. The
AUTO_INCREMENTis MySQL specific attribute. The
AUTO_INCREMENTattribut can be defined as
IDENTITYin SQL server,
PRIMARY KEY constraint that consists of more than one columns example
Let’s take a look at the
orderdetails table in the sample database:
CREATE TABLE orderdetails (
OrderID int(11) NOT NULL,
ProductID int(11) NOT NULL,
UnitPrice decimal(19,4) NOT NULL,
Quantity smallint(6) NOT NULL,
Discount float NOT NULL,
PRIMARY KEY (OrderID,ProductID),
orderdetails table, we have a primary key that consists of two columns:
ProductID. Therefore, the primary key constraint has to be defined at the table level.
In this tutorial, you have learned about the
PRIMARY KEY constraint to use it in tables to enforce data integrity automatically.