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 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 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. 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, SERIAL in 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.