The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Primary and Foreign Keys

Next ►
►

Introduction

In relational database design, primary and foreign keys are fundamental concepts that ensure data integrity and establish relationships between tables. This section will cover:

  1. Primary Keys: What they are, their importance, and how to define them.
  2. Foreign Keys: Their role in establishing relationships between tables and how to implement them.

Primary Keys

What is a Primary Key?

A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified and accessed. The primary key must contain unique values and cannot contain NULL values.

Importance of Primary Keys

  • Uniqueness: Ensures that each record is unique.
  • Indexing: Automatically creates a unique index, which improves query performance.
  • Data Integrity: Prevents duplicate records and ensures that each record can be uniquely identified.

Defining a Primary Key

You can define a primary key when creating a table or by altering an existing table.

Example: Defining a Primary Key During Table Creation

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example:

  • employee_id is defined as the primary key.
  • SERIAL is a PostgreSQL-specific data type that auto-increments the value.

Example: Adding a Primary Key to an Existing Table

ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

In this example:

  • pk_employee_id is the name of the primary key constraint.

Foreign Keys

What is a Foreign Key?

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The foreign key establishes a relationship between the two tables.

Importance of Foreign Keys

  • Referential Integrity: Ensures that the value in the foreign key column exists in the referenced primary key column.
  • Data Consistency: Prevents actions that would destroy links between tables.
  • Cascading Actions: Allows for cascading updates and deletes, which can simplify data management.

Defining a Foreign Key

You can define a foreign key when creating a table or by altering an existing table.

Example: Defining a Foreign Key During Table Creation

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) 
        REFERENCES customers (customer_id)
);

In this example:

  • customer_id in the orders table is a foreign key that references customer_id in the customers table.
  • fk_customer is the name of the foreign key constraint.

Example: Adding a Foreign Key to an Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) 
REFERENCES customers (customer_id);

In this example:

  • fk_customer is the name of the foreign key constraint.

Practical Exercises

Exercise 1: Creating Tables with Primary and Foreign Keys

  1. Create a departments table with the following columns:

    • department_id (Primary Key)
    • department_name

  2. Create an employees table with the following columns:

    • employee_id (Primary Key)
    • first_name
    • last_name
    • department_id (Foreign Key referencing departments)

Solution

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id) 
        REFERENCES departments (department_id)
);

Exercise 2: Adding Foreign Key Constraints

  1. Create a projects table with the following columns:

    • project_id (Primary Key)
    • project_name
    • employee_id (Foreign Key referencing employees)

  2. Add the foreign key constraint to the projects table.

Solution

CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100),
    employee_id INT
);

ALTER TABLE projects
ADD CONSTRAINT fk_employee
FOREIGN KEY (employee_id) 
REFERENCES employees (employee_id);

Common Mistakes and Tips

  • NULL Values in Primary Keys: Ensure that primary key columns do not allow NULL values.
  • Data Type Mismatch: Ensure that the data types of the foreign key and the referenced primary key match.
  • Cascading Actions: Use cascading actions (e.g., ON DELETE CASCADE) carefully to avoid unintended data loss.

Conclusion

Understanding primary and foreign keys is crucial for designing robust and efficient relational databases. Primary keys ensure that each record is unique, while foreign keys establish and enforce relationships between tables. By mastering these concepts, you can ensure data integrity and consistency in your PostgreSQL databases.

Next, we will delve into Indexes, which are essential for optimizing query performance and ensuring efficient data retrieval.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved