PostgreSQL: Defining and Managing Primary Keys

Ensure data integrity with primary keys in PostgreSQL. This guide explains how to define and manage primary key constraints on tables.

Defining Primary Keys in PostgreSQL

In PostgreSQL, a primary key uniquely identifies each row in a table. This ensures data integrity and helps optimize database operations. Primary keys can consist of a single column or multiple columns. This guide explains how to define primary keys using both `CREATE TABLE` and `ALTER TABLE` commands.

Understanding Primary Keys

A primary key is a constraint applied to one or more columns in a table. It has two main properties:

• Uniqueness: All values in the primary key column(s) must be unique.
• Not Null: The primary key column(s) cannot contain NULL values.

A table can have only one primary key.

Creating a Primary Key Using `CREATE TABLE`

You can define a primary key when creating a table using the `CREATE TABLE` command. There are two ways to do this:

1. Specifying the Primary Key on a Single Column

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY, 
    column2 data_type,
    ...
);

2. Specifying the Primary Key on Multiple Columns

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column1, column2)
);

The `CONSTRAINT` clause allows you to specify a custom name for your primary key constraint. It's recommended to use `INT` or `BIGINT` for primary key columns.

Example: Creating a Table with a Primary Key

(Note: The example below uses a SERIAL primary key, which automatically generates sequential integer values. Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success. The descriptions below aim to convey the information present in those screenshots.)

CREATE TABLE Applicants (
    applicant_id SERIAL PRIMARY KEY,
    applicant_username VARCHAR(50),
    applicant_password VARCHAR(30),
    applicant_email VARCHAR(55)
);

This creates a table named `Applicants` with `applicant_id` as the primary key. `SERIAL` automatically generates unique integer values for this column.

Adding a Primary Key Using `ALTER TABLE`

You can also add a primary key to an existing table using the `ALTER TABLE` command. This is useful if you initially did not define a primary key and need to add one later. It is less common to add a primary key after the table has already been created and populated.

ALTER TABLE table_name ADD PRIMARY KEY (column1 [, column2, ...]);

You can use the `CONSTRAINT` clause to specify the primary key constraint's name.

Example: Adding a Primary Key to an Existing Table

(Note: This would require a pre-existing table. Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success.)

ALTER TABLE Trainees ADD PRIMARY KEY (trainee_ID, trainee_roll_No);

Managing Primary Keys in PostgreSQL

In PostgreSQL, a primary key uniquely identifies each row in a table. This is crucial for data integrity. This guide explains how to add and remove primary keys using SQL commands, including how to create auto-incremented primary keys.

Adding a Primary Key Using `CREATE TABLE`

You can define a primary key when initially creating a table using the `CREATE TABLE` command. The primary key constraint ensures that all values in the primary key column(s) are unique and not NULL. You can specify a primary key on one or more columns.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,  -- Single-column primary key
    customer_name VARCHAR(45),
    customer_age INT,
    customer_address VARCHAR(25)
);

CREATE TABLE Products (
    product_id INT,
    product_name VARCHAR(100),
    category_id INT,
    CONSTRAINT products_pkey PRIMARY KEY (product_id, category_id) --Multi-column primary key
);

(Note: Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success.)

The examples show creating tables with both single-column and multi-column primary keys. You can optionally specify a name for your primary key constraint using `CONSTRAINT constraint_name`.

Adding a Primary Key Using `ALTER TABLE`

To add a primary key to an existing table, use the `ALTER TABLE` command. This is less common, as it's generally better practice to define primary keys when initially creating tables.

ALTER TABLE Customers ADD PRIMARY KEY (customer_id);

Adding an Auto-Incremented Primary Key

PostgreSQL's `SERIAL` data type creates a sequence that automatically generates unique integer values. You can use this to create an auto-incrementing primary key.

CREATE TABLE Sellers (
    seller_id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

(Note: Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success.)

Removing a Primary Key Using `ALTER TABLE`

To remove a primary key constraint, use the `ALTER TABLE` command with the `DROP PRIMARY KEY` or `DROP CONSTRAINT` clause.

ALTER TABLE Applicants DROP PRIMARY KEY;  -- Removes the primary key constraint
ALTER TABLE Applicants DROP CONSTRAINT applicants_pkey; --Removes the primary key by constraint name