PostgreSQL - Primary Key

A primary key in PostgreSQL is a column (or a set of columns) that uniquely identifies each row in a table. It is an important component for ensuring data integrity and plays an important role in database normalization. When we define a primary key in PostgreSQL, the database automatically creates a unique B-tree index on the key column(s), which helps improve query performance.

In this article, we will explain Primary Key in PostgreSQL and its importance in ensuring data integrity. We will also cover the syntax for defining primary keys, examples of how to implement them in tables, and explore various use cases like auto-incrementing primary keys and composite keys

What is Primary Key in PostgreSQL ?

Technically, a primary key constraint combines two aspects: a NOT NULL constraint (which means the key cannot be left empty) and a UNIQUE constraint (which ensures no two rows have the same key value). It is important to note that each table can have only one primary key, but this key can consist of multiple columns, known as a composite key.

Syntax

column_name Data-type PRIMARY KEY

Examples of PostgreSQL Primary Key

Now let's look into some examples of the Primary Key in PostgreSQL to better understand the concept and see how it ensures data uniqueness and integrity in real-world scenarios.

Example 1: Creating a Table with a Primary Key,

In this example, we create a table called books with the book_id column as the Primary Key to uniquely identify each record. The PRIMARY KEY constraint ensures that the book_id values are unique and not NULL, thus maintaining data integrity.

Query:

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    price INTEGER
);

INSERT INTO books(book_id, title, price)
VALUES
    ('101', 'Jobs', '2000'),
    ('102', 'Geeta', '250'),
    ('103', 'Ramayana', '354'),
    ('104', 'Vedas', '268');

SELECT * FROM books;

Output

Example 2: Adding a Primary Key to an Existing Table

If we have an existing table and need to add a primary key, we can do so with the ALTER TABLE statement. In this example we will be adding PRIMARY key to an existing table. Then, table named 'vendors' will be created with the help of the below given PostgreSQL query.

Query:

CREATE TABLE vendors (name VARCHAR(255));

INSERT INTO vendors (NAME)
VALUES
    ('Microsoft'),
    ('IBM'),
    ('Apple'),
    ('Samsung'); 

SELECT * FROM  vendors;

Output

Example 3: Adding an Auto-Incremented Primary Key

If we want to add a primary key named 'id' to the 'vendors' table and make the 'id' field auto-increment by a single unit. We can achieve this using the SERIAL type, which automatically generates a unique value for each row using the following statement.

Query:

ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY;

Now, the id column will automatically generate unique values for each row. Let’s check the vendors table again using the below command:

SELECT id, name FROM vendors;

Output

Important Points About PostgreSQL Primary Key

• When WE define a primary key, PostgreSQL automatically creates a unique B-tree index on the column(s) used.
  
• Use SERIAL or BIGSERIAL for auto-incrementing primary keys. These types automatically generate unique sequential values.
  
• Each table can have only one primary key, but that key can consist of multiple columns (composite key).
  
• In tables that use inheritance, primary keys are not automatically inherited. You must explicitly define primary keys in each child table.

Conclusion

In conclusion, the PostgreSQL primary key is essential for uniquely identifying each row in a table, ensuring data integrity and efficient querying. A unique primary key guarantees that no two rows can have the same value for the primary key column(s).

When using an auto-increment primary key, PostgreSQL can automatically generate unique values, making it ideal for identifying records sequentially. In cases where multiple columns are needed to define uniqueness, a composite primary key can be used.