In all the databases, including PostgreSQL, the Primary keys are used to identify a record uniquely. It can be defined using primary key constraints. In PostgreSQL databases, the primary key ensures the relationship between two tables. But if there is only one table, that does not associate with any other table. For this, it does not require any primary key.

The purpose of this tutorial is to present the complete guidance of the primary key along with practical implementation.

• Usage of Primary Key in PostgreSQL
• Example 1: Creation of Primary key via CREATE TABLE Statement
• Example 2: Removal of Primary key via ALTER TABLE Statement
• Example 3: Altering a Primary key via ALTER TABLE Statement

Let's start an interesting journey.

Usage of Primary Key in PostgreSQL

To create a primary key in Postgres, the user must specify the keyword “PRIMARY KEY” along with the targeted column.

Syntax

The following syntax shows how a primary key is created during table creation:

CREATE TABLE table_name(
column_1 data_type PRIMARY KEY, 
column_2 data_type
);

The above syntax has some parameters which are enlisted as below:

- CREATE TABLE statement is utilized for creating a table.
- table_name specifies the name of the created table in the database.
- column_1 represents the name of the column.
- data_type identifies the data type of the specified column.
- PRIMARY KEY is a keyword to create a Primary key in a table.

Example 1: Creation of Primary Key via CREATE TABLE Statement

This example demonstrates how to create a primary key while creating a table. The CREATE TABLE statement is utilized for creating a table named “org_table”. After that, specify the column's name and write the primary key name as a constraint:

CREATE TABLE org_table (
emp_id INTEGER CONSTRAINT off_pk PRIMARY KEY,
name TEXT,
age INTEGER
);

The above figure shows that a column “emp_id” is set as the primary key during creating a table. We specified the name of the primary key constraint as “off_pk” as shown in the above figure.

Let’s execute the below-given command to describe the structure of the org_table:

\d org_table;

Users can verify that the primary key is assigned to the “emp_id” column of table “org_table”.

Example 2: Removal of Primary key via ALTER TABLE Statement

Removing the primary key is also an important concept in the PostgreSQL database. In Postgres, the “ALTER TABLE” statement must be used with the “DROP CONSTRAINT” command to drop the primary key:

ALTER TABLE org_table DROP CONSTRAINT off_pk;

When dropping a primary key constraint, the user must specify the constraint name instead of specifying the column name:

In the above figure, the “ALTER TABLE” message confirms that the primary key named “off_pk” has been successfully removed from the targeted table. Run the \d command to describe the updated table:

\d org_table;

Users can verify that the primary key constraint has been dropped from the org_table.

Example 3: Altering Primary key via ALTER TABLE Statement

Let’s add a new column to the org_table using ALTER TABLE statement:

ALTER TABLE org_table ADD COLUMN id_card VARCHAR;

The “ALTER TABLE” message is displayed in the output which confirms that a new column “id_card” has been added to the “org_table”.

Let’s describe the structure of “org_table” using the following command:

\d org_table;

The output shows that the column named “id_card” has been added to the org_table successfully. Suppose we want to alter the behavior of the id_card column (i.e. id_card column should be uniquely identified), to do so, we will execute the ALTER command as follows:

ALTER TABLE org_table ADD PRIMARY KEY (id_card);

For displaying the table details, users can utilize the “\d” command as follows:

\d org_table;

The resultant output clarifies that the PRIMARY KEY constraint has been added to the id_card column.

That’s it! You have learned all the aspects of the primary key in PostgreSQL.

Conclusion

To create a primary key in Postgres, user must specify the “PRIMARY KEY” keyword along with the name of the targeted column. Usually, primary keys are created when a table is created, but they can also be assigned to existing columns. This tutorial demonstrates the syntax, usage, and practical implementation of the primary key in PostgreSQL.