PostgreSQL Primary Key: CREATE and ALTER TABLE Statements

Referential Integrity is a must when it comes to proper relational database design. The consistent usage of primary keys can help you ensure the best practices of RDBMS are adhered to. In this article, we are going to focus on using PostgreSQL primary keys while creating a new table or editing the existing one. We will provide you with detailed instructions spiced up with the corresponding code examples and screenshots based on the PostgreSQL GUI tool for better visualization – dbForge Studio for PostgreSQL.

Contents

• What is a primary key in PostgreSQL
  • Why should you use Postgres primary key
• How to add a primary key in PostgreSQL
• Create primary key using CREATE TABLE statement
  • Syntax
  • Example
• Create primary key using ALTER TABLE statement
• Add primary key constraints to existing tables
• Create a UUID primary key
• Add an auto-incremented primary key
• How to change existing primary key
• How to drop primary key in PostgreSQL
• Advantages of using PostgreSQL GUI tool to manage keys
• Conclusion

What is a primary key in PostgreSQL

Let us begin with the basics and define the primary key. It is a special relational database table column (or a combination of columns) designed to uniquely identify all table records. A primary key must contain a unique value for each row of data, and it cannot contain any NULL values. It can be either a column that is specially generated by a database according to a specified sequence or an already existing in a database column.

Why should you use Postgres primary key

In the relational data model, there is a mandatory requirement for object identification by means of the unique set of property values. In other words, the values in at least one of the columns must be unique and appear only once.

The main reason why you should use primary keys is to implement a relationship between two tables in a PostgreSQL database. If there is a table in your database that does not relate to any other table, a primary key is not needed. Otherwise, it is better to use it in order to eliminate duplication of data, reconcile it, and speed up its processing.

The primary key constraint can consist of one (simple) or more (composite) fields. Occasionally, one field is not enough to uniquely identify records in the database. In such cases, you can use a combination of several fields as a composite primary key.

How to add a primary key in PostgreSQL

When it comes to adding a primary key constraint in PostgreSQL, there are several ways to do that depending on the situation:

• CREATE TABLE command
• ALTER TABLE command

Note: ALTER TABLE statement can be used both for adding and removing the primary key constraint.

Create primary key using CREATE TABLE statement

The most common scenario is planning your database structure beforehand. You can determine what the primary keys will be at the stage of table creation. This way, you are going to establish the structure of the database and you will be able to create relations between the columns later on.

Syntax

Let us take a look at the basic syntax of the PostgreSQL CREATE TABLE statement with the primary key:

CREATE TABLE table_name (
  column1 column_definition,
  column2 column_definition,
  ...
  CONSTRAINT [constraint_name] 
   PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
);

dbForge Studio for PostgreSQL offers a set of predefined code snippets that look like this:

The snippets form a common syntactic structure of the queries and you only need to fill in the fields without having to think about the syntax itself.

Example

Once you substitute the placeholders with the real table, column, and constraint names, specify the data types, and configure the PRIMARY KEY clause, the query will adopt a more practical shape:

As soon as you execute the command, you will see that a new table named users is added to the database. Inside the table, there are three columns, two of which are primary keys.

Create primary key using ALTER TABLE statement

Even though the primary keys are usually determined during the table creation, there are cases when you need to transform the ones that already exist. This usually happens when a database undergoes some restructuration. In such cases, use the ALTER TABLE command along with a PRIMARY KEY constraint.

Add primary key constraints to existing tables

When aiming for adding a primary key to an existing column, the syntax will look similar to this example:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

Let us take a look at the same query, as to say, in its natural habitat. Now, we are going to alter the users table using a convenient PostgreSQL IDE by dbForge:

PostgreSQL Code Snippets feature saves your time and improves code quality when writing and editing queries.

Create a UUID primary key

If required, you can use UUID as a primary key in PostgreSQL. However, there are no functions for generating the UUID values by default. Therefore, it relies on third-party modules that provide specific algorithms for that purpose. For instance, the uuid-ossp module can provide some useful functions that implement standard algorithms for generating UUIDs.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Once done, use the uuid_generate_v1() function to generate the UUID value based on the combination of the MAC address, current timestamp, and a random value:

For more information on the functions for UUID generation, turn to PostgreSQL uuid-ossp module documentation.

Add an auto-incremented primary key

There could be occasions where you build and maintain tables in PostgreSQL and would like particular values for a column to be generated on request. This would be rather applicable for ID columns that usually act as the primary key of a table. This is when the SERIAL pseudo-type takes the stage:

CREATE TABLE table_name ( id SERIAL );

When the CREATE TABLE query does not include the SERIAL keyword, you will have to populate the primary key column with values by means of the INSERT statement. However, SERIAL is a game-changer in this case: you do not need to insert anything into the primary column as the values are generated automatically:

How to change an existing primary key

It is important to remember that there can be only one primary key in a table, though, this primary key can consist of single or multiple columns (fields). Therefore, if you have already defined one for a PostgreSQL table, you will not be able to add a different one. The way out of this situation will be to delete the existing key using a DROP clause within an ALTER TABLE statement and then add the new primary key.

How to drop primary key in PostgreSQL

You might also face a situation where you need to remove the primary key constraint from a particular table, but keep the table itself. Then, you are going to customize the ALTER TABLE statement a little bit by adding the DROP CONSTRAINT clause to it:

ALTER TABLE table_name
  DROP CONSTRAINT [constraint_name];

Specify the name of the table you are willing to edit as well as the name of the primary key that is about to get deleted.

As a result, you can see that both columns that used to be part of the primary key are now just regular ones.

Note: Use caution while deleting primary keys since dropping such constraints can lead to data duplication in the future.

Advantages of using PostgreSQL GUI tool to manage keys

Previously in this article, we have been using dbForge Studio for PostgreSQL to provide you with visual examples of how to deal with the primary keys. It is a GUI client and universal tool for PostgreSQL database development and management. This IDE allows users to create, develop, and execute queries, edit, and adjust the code according to their requirements in a convenient and user-friendly interface. dbForge Studio for PostgreSQL offers many useful tools, for example, code formatting, Postgres Pivot Tables, and many more.

Conclusion

To sum up, using primary keys in database development and administration is not essentially required, but is highly recommended. If you are willing to protect your database from errors and duplicate data that slows down its work, primary keys can assist you with that task. User-friendly GUI, such as dbForge Studio for PostgreSQL, can also significantly improve your everyday working experience.