PRIMARY KEY Constraint

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 the PRIMARY KEY constraint

A 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 the primary key of the table. If you insert or update a row that would cause a duplicate primary key, SQL engines will issue an error 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 BLOB, CLOB, ARRAY or NCLOB.

We often create a primary key constraint 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 and Message. The 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
)Code language: SQL (Structured Query Language) (sql)

The 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 NULL attribute implicitly.
AUTO_INCREMENT: the database engine generates a sequence for the column whenever a new row is inserted into the table. The AUTO_INCREMENT is MySQL-specific attribute. The AUTO_INCREMENT attribute can be defined as IDENTITY in SQL server and PostgreSQL.

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),
 )Code language: SQL (Structured Query Language) (sql)

In the orderdetails table, we have a primary key that consists of two columns: OrderID and 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.

Introduction to the PRIMARY KEY constraint

PRIMARY KEY constraint examples

PRIMARY KEY constraint that consists of 1 column example

PRIMARY KEY constraint that consists of more than one columns example

Databases