The primary key constraint refers to a unique column or a group of columns that can uniquely identify each record in a given database table. By setting a column as the primary key of a given table, you ensure that the values that are stored in the column are unique and there are no NULL values.
In most cases, you define the primary key column or columns during the table creation. However, you may encounter such scenarios where you need to change the primary key of an existing table.
In this tutorial, we will explore how you can change the primary key of a given database table to another column.
Requirements:
To follow along with this tutorial, we assume that you have the following:
- Basic understanding of PostgreSQL and SQL.
- Installed and running PostgreSQL server.
- Access to a database with appropriate privileges to modify the table structures.
What Is the Role of Primary Keys in a Table?
The primary key constraint plays two significant roles in a given database table. These include:
Data Uniqueness – Each value in the primary key column(s) must be unique across all rows in the table. This helps to prevent duplicate records in the defined columns which makes the data unique.
Row Identification – A primary key acts as an identifier for each row which allows for efficient referencing and joining with other tables.
In most cases, you will find the primary key column implemented as a serial column which allows the database engine to generate values as they are added automatically.
However, depending on the requirements, you can specify a primary key to include more than one column which is also known as a composite key.
Sample Table
To demonstrate how we can change the primary key of an existing table, let us start by creating a test table called “employees” as shown in the following query:
id SERIAL PRIMARY KEY,
name VARCHAR(255),
age INTEGER,
department_id INTEGER
);
The given query creates a table called “employees” with the columns that are defined as follows:
- The id column as a SERIAL PRIMARY KEY. This creates auto-generated integer values which serve as the primary key for the table.
- The name column as a VARCHAR(255). This allows up to 255 characters for the values that are stored in the column.
- The age column as an INTEGER type.
- The department column as an INTEGER value.
Change the Primary Key in PostgreSQL
Let us now demonstrate how to change the primary key of a given table. For example, suppose we wish to change the primary key of the “employees” table from the id to the employee_number column.
Drop the Existing Primary Key
The first step is to remove the existing primary key from the table. Then, we can use the ALTER TABLE and DROP CONSTRAINT clauses for that.
The syntax is as follows:
DROP CONSTRAINT <table_name>_pkey;
Example:
DROP CONSTRAINT employees_pkey;
In some cases, the primary key name is auto-generated by the database engine.
Create a New Primary Key Constraint
Once you remove the existing primary key, you can create a new one and define the new column as the primary key.
ADD PRIMARY KEY (column_name);
Example:
ADD PRIMARY KEY (employee_number);
This should add a new primary key to the “employees” table and use the employee_number column.
NOTE: Before setting a column as the primary key, ensure that they satisfy the uniqueness and not null constraints.
Remove the Primary Key on Cascade
In most cases, you will find that the primary key of a given table has been used as a foreign key in another table. In such as case, the drop constraint nulifies the primary key in the other table.
To prevent this, you can use the CASCADE command in the DROP CONSTRAINT command as follows:
DROP CONSTRAINT constraint_name CASCADE;
Using the CASCADE option, we tell PostgreSQL to automatically drop any dependent objects, such as indexes or foreign key constraints, that rely on the constraint that we delete.
Conclusion
We explored the primary key constraint in PostgreSQL databases. In addition, we explored the role of primary key constraints, how to create one, and how to change the primary key of an existing table.
