PostgreSQL primary key

You will learn how to create, drop, disable, and enable a primary key in PostgreSQL with syntax and examples.

What is the primary key in PostgreSQL?

In PostgreSQL, a primary key is a single field or a combination of fields that uniquely define a record. None of the fields that are part of the primary key can contain the value NULL. A table can have only one primary key.

Note:

• In PostgreSQL, the primary key is created using the CREATE TABLE operator or ALTER TABLE operator.
  You use ALTER TABLE in PostgreSQL to add or delete the primary key.

Create a primary key with the CREATE TABLE operator

You can create a primary key in PostgreSQL using the CREATE TABLE operator.

The syntax for creating a primary key with CREATE TABLE in PostgreSQL

CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
…
CONSTRAINT constraint_name
PRIMARY KEY (index_col1, index_col2,... index_col_n)
);

OR

CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
…
);

• table_name – The name of the table you want to create.
• column1, column2 – The columns you want to create in the table.
• Table_name – Name of the primary key.
• index_col1, index_col2,… index_col_n – The columns that make up the primary key.

Consider an example of how to create a primary key using the CREATE TABLE operator in PostgreSQL.

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
order_id integer NOT NULL,
order_date date,
size integer,
notes varchar(200),
CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

Or you can also create a primary key in the order_details table using the following syntax:

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
order_id integer NOT NULL,
order_date date,
size integer,
notes varchar(200)
);

In these two examples, we created a primary key in the order_details table named order_details_pk. It consists of only one column, the order_detail_id column.

We can also create a primary key with more than one field, as in the example below:

CREATE TABLE order_details
( order_date date NOT NULL,
customer_id integer NOT NULL,
size integer,
notes varchar(200),
CONSTRAINT order_details_pk PRIMARY KEY (order_date, customer_id)
);

In this example, a primary key is created with the name order_details_pk, which consists of a combination of order_date and customer_id columns. Thus, each combination of order_date and customer_id must be unique in the order_details table.

Create a primary key using the ALTER TABLE operator

You can create a primary key in PostgreSQL using the ALTER TABLE operator.

The syntax for creating a primary key using the ALTER TABLE operator in PostgreSQL

ALTER TABLE table_name
ADD CONSTRAINT [ constraint_name ]
PRIMARY KEY (index_col1, index_col2,... index_col_n)

• table_name – The name of the table to change.
• constraint_name – The name of the primary key.
• index_col1, index_col2,… index_col_n – The columns that make up the primary key.

Consider an example of how to create a primary key using the ALTER TABLE operator in PostgreSQL.

ALTER TABLE order_details
ADD CONSTRAINT order_details_pk
PRIMARY KEY (order_detail_id);

In this example, we created a primary key for an existing order_details table named order_details_pk. It consists of an order_detail_id column.

We can also create a primary key with more than one field, as in the example below:

ALTER TABLE order_details
ADD CONSTRAINT order_details_pk
PRIMARY KEY (order_date, customer_id);

In this example, we have created a primary contact_pk key which consists of a combination of last_name and first_name columns.

Drop the primary key

You can delete a primary key in PostgreSQL using the ALTER TABLE operator.

The syntax for removing the primary key in PostgreSQL

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

• table_name – The name of the table to change.
• constraint_name – The name of the primary key that you want to delete.

Let’s consider an example of how to delete a primary key using the ALTER TABLE operator in PostgreSQL.

ALTER TABLE order_details
DROP CONSTRAINT order_details_pk;

In this example, we have removed the primary key from the contacts table. We do not need to give the name of the primary key, because there can only be one key in the table.

PostgreSQL: What Are Primary Keys | Course