PostgreSQL

Chapter 8 - RDBMS Concepts

Primary Key

In PostgreSQL, a primary key is a crucial element of relational database design. It uniquely identifies each record in a table, ensuring that no two rows have the same primary key value. This uniqueness constraint helps maintain the integrity of the database by preventing duplicate records and ensuring that each row can be uniquely accessed. The primary key is also used to establish relationships between tables, which is essential for creating a well-organized and efficient database schema.

Here's a step-by-step guide on how to define and use primary keys in PostgreSQL, with code examples related to banking, customers, accounts, and transactions:

Creating a Table with a Primary Key
To define a primary key in PostgreSQL, you use the PRIMARY KEY constraint. This constraint ensures that the column(s) designated as the primary key will have unique values and not contain NULL values.
```
-- Creating a table for customers with a primary key
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);
```
- customer_id is defined as SERIAL, which automatically increments with each new record.
- It is marked as the PRIMARY KEY, ensuring uniqueness.
Defining a Composite Primary Key
Sometimes, a single column is not sufficient to uniquely identify a row. In such cases, you can use a composite primary key, which consists of two or more columns.
```
-- Creating a table for accounts with a composite primary key
CREATE TABLE accounts (
    account_number INT,
    customer_id INT,
    balance DECIMAL(10, 2),
    PRIMARY KEY (account_number, customer_id)
);
```
- The primary key is a combination of account_number and customer_id, ensuring that each combination is unique.
Altering a Table to Add a Primary Key
If you need to add a primary key to an existing table, you can use the ALTER TABLE statement.
```
-- Adding a primary key to the transactions table
ALTER TABLE transactions
ADD CONSTRAINT pk_transaction_id PRIMARY KEY (transaction_id);
```
- pk_transaction_id is the name of the primary key constraint being added to the transactions table.
Removing a Primary Key
If a primary key constraint needs to be removed, you can use the ALTER TABLE statement to drop it.
```
-- Dropping the primary key constraint from the accounts table
ALTER TABLE accounts
DROP CONSTRAINT accounts_pkey;
```
- accounts_pkey is the name of the primary key constraint being dropped.

Examples for a Complete Banking System

Here's how you might set up primary keys for a simple banking system:

Customers Table: customer_id as the primary key.
Accounts Table: Composite primary key consisting of account_number and customer_id.
Transactions Table: transaction_id as the primary key.

-- Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

-- Accounts table
CREATE TABLE accounts (
    account_number INT,
    customer_id INT,
    balance DECIMAL(10, 2),
    PRIMARY KEY (account_number, customer_id)
);

-- Transactions table
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_number INT,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (account_number) REFERENCES accounts (account_number)
);

Each table is designed to ensure data integrity and efficient querying by using appropriate primary keys.

This guide should help beginners understand the concept and implementation of primary keys in PostgreSQL, with practical examples related to banking and customer management.

Detailed Breakdown of Primary Key Characteristics and Purposes
Uniqueness
Guaranteed Uniqueness: row in a table is uniquely identified by the primary key, meaning no two rows can have the same primary key value. This uniqueness guarantee is crucial for accurately identifying and interacting with individual records.
Non-nullability
Cannot be Null:A primary key column cannot have a NULL value. Every row must have a primary key value to ensure that it can be uniquely identified. This rule helps maintain data integrity by preventing records from becoming "invisible" or unidentifiable due to a lack of an identifier.
Immutability
Stability and Permanence:Once assigned, the primary key value of a record should not change. While this is more of a best practice than a strict rule enforced by all database systems, it's important for maintaining data integrity and consistency over time. Changing primary key values can lead to issues with data relationships and integrity.
Indexing
Automatic Indexing:Most database systems automatically create an index for the primary key, making search and retrieval operations much faster. An index, in this context, is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
Simplification of Relationships
Facilitating Relationships:In relational databases, primary keys play a crucial role in establishing relationships between tables. A primary key from one table can be referenced in another table to create a "foreign key" relationship, enabling the relational database to link data across tables efficiently. This relationship is foundational for operations like joins, which combine data from multiple tables based on these keys.
Composite Primary Keys
Single or Multiple Columns:While a primary key can be a single column, it can also consist of multiple columns. This is known as a composite primary key. It is used when no single column uniquely identifies each row, but a combination of columns does. Each part of this composite key must be part of the primary key in every row.
Use Cases and Examples
For example, in a "Users" table, the primary key might be a "UserID" column, where each user is assigned a unique identifier. In an "Orders" table, the primary key could be an "OrderID." If an order can be uniquely identified by combining "OrderDate" and "OrderNumber", these two columns together could serve as a composite primary key.
Conclusion
Primary keys are a cornerstone of database design, ensuring data integrity, improving performance, and enabling the relational model's powerful data linkage capabilities. Properly defining primary keys is essential for effective database and application design, ensuring that data remains consistent, accessible, and meaningful.
Understanding Primary Keys through a Library Analogy
Imagine you're in a library full of books. Each book has a unique identification number, often called an ISBN (International Standard Book Number). This number is specific to that book and no other book in the library (or the world) has the same ISBN. When you want to find a specific book, you can just use this number to locate it quickly among thousands of others.
In database terms, this ISBN acts like a primary key for the book in the library's catalog system. Just as the ISBN uniquely identifies each book, a primary key in a database table uniquely identifies each record. This means that every record, or row of data, in a table can be quickly and precisely accessed by its primary key, avoiding any mix-up or duplication among the vast amount of data stored in the database.
TABLES WITH FIRST COLUMNS AS PRIMARY KEY