Understanding the difference between primary and foreign key

Understanding the distinction between primary and foreign keys is fundamental when working with relational databases. Both primary and foreign keys play crucial roles in establishing relationships between tables. In this article, we will delve into the differences between primary and foreign keys, using a real-time example to illustrate their significance in maintaining data integrity.

Primary Key: A primary key is a column or a set of columns in a database table that uniquely identifies each record in the table. It ensures that no two records have the same values for the primary key column(s). The primary key serves as a unique identifier for each row in the table and is used to establish relationships with other tables in a relational database. Typically, a primary key is defined when creating a table and is essential for data integrity and efficient querying.

Foreign Key: A foreign key is a column or a set of columns in a table that establishes a link or a relationship between two tables in a relational database. It refers to the primary key of another table, thereby creating a connection between the two tables based on a shared value. The foreign key constraint ensures referential integrity, meaning that the values in the foreign key column(s) must exist in the referenced primary key column(s) of the related table. The foreign key enables the creation of relationships and enforces data consistency and integrity across tables. It helps maintain the integrity of relational databases by preventing invalid or inconsistent data.

Difference between Primary and Foreign Keys:

• Uniqueness: The primary key ensures the uniqueness of each record in the table, while the foreign key establishes relationships between tables.
• Purpose: The primary key uniquely identifies records within a table, acting as a unique identifier. The foreign key establishes relationships with the primary key in another table, enforcing referential integrity and maintaining data consistency.
• Usage: The primary key is used within the table where it is defined, while the foreign key is used to establish relationships with another table.
• Constraints: The primary key column has the constraint of uniqueness, ensuring that no duplicate values exist. The foreign key column references the primary key of another table, enforcing referential integrity constraints.

Practical Example: Employee and Department Tables

Let's consider a real-time example involving an Employee and Department tables. We have two tables: "Employee" and "Department." The "Employee" table contains information about employees, including EmployeeID, Name, Position, and DepartmentID. The "Department" table contains details about different departments within the organization, with columns such as DepartmentID and DepartmentName.

In the "Employee" table, the primary key is the EmployeeID column. The primary key uniquely identifies each employee record in the table. It serves as a unique identifier and ensures that no two employees have the same EmployeeID. The primary key enforces data uniqueness and acts as a reference point for establishing relationships with other tables.

In the "Employee" table, the DepartmentID column is a foreign key. This column establishes a relationship with the "Department" table, specifically the DepartmentID column, which acts as the primary key in that table. The foreign key in the "Employee" table references the primary key in the "Department" table, indicating the department to which each employee belongs. The foreign key maintains referential integrity by ensuring that an employee cannot be assigned to a non-existent department.

Here's an example of creating the "Employee" and "Department" tables with primary and foreign keys based on the previous real-time example:

sql
-- Create the Department table
CREATE TABLE Department (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(50)
);

-- Create the Employee table
CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(50),
  Position VARCHAR(50),
  DepartmentID INT,
  FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

In the above SQL code, we first create the "Department" table with a primary key on the "DepartmentID" column. The primary key ensures the uniqueness of each department record in the table.

Next, we create the "Employee" table with a primary key on the "EmployeeID" column. The primary key guarantees the uniqueness of each employee record in the table.

The "Employee" table also includes a "DepartmentID" column, which acts as a foreign key. It establishes a relationship with the "Department" table, specifically referencing the "DepartmentID" column in that table. This foreign key constraint ensures that an employee cannot be assigned to a non-existent department, maintaining referential integrity.

By creating these tables with primary and foreign keys, we establish a relationship between the "Employee" and "Department" tables, facilitating efficient querying and ensuring data consistency in our database.

inserting data into the "Department" and "Employee" tables and retrieving the result:

sql
- Insert data into the Department table
INSERT INTO Department (DepartmentID, DepartmentName)
VALUES (1, 'Sales'),
       (2, 'Marketing'),
       (3, 'Finance'),
       (4, 'HR');

-- Insert data into the Employee table
INSERT INTO Employee (EmployeeID, Name, Position, DepartmentID)
VALUES (1, 'John Doe', 'Manager', 1),
       (2, 'Jane Smith', 'Sales Representative', 1),
       (3, 'Michael Johnson', 'Marketing Coordinator', 2),
       (4, 'Emily Brown', 'Finance Analyst', 3),
       (5, 'David Lee', 'HR Assistant', 4);

Now, let's retrieve the data from both tables using a SELECT query:

sql
-- Retrieve data from the Department table
SELECT * FROM Department;

-- Retrieve data from the Employee table
SELECT * FROM Employee;

The first SELECT query will display the data from the "Department" table:

diff
+-------------+---------------+
| DepartmentID| DepartmentName|
+-------------+---------------+
|      1      |     Sales     |
|      2      |   Marketing   |
|      3      |    Finance    |
|      4      |       HR      |
+-------------+---------------+

The second SELECT query will retrieve the data from the "Employee" table:

sql
+-------------+--------------+-------------------------+-------------+
| EmployeeID  |     Name     |        Position         | DepartmentID|
+-------------+--------------+-------------------------+-------------+
|      1      |   John Doe   |        Manager          |      1      |
|      2      |  Jane Smith  |  Sales Representative   |      1      |
|      3      |Michael Johnson|  Marketing Coordinator |      2      |
|      4      | Emily Brown  |     Finance Analyst     |      3      |
|      5      |   David Lee  |       HR Assistant      |      4      |
+-------------+--------------+-------------------------+-------------+

The above results show the inserted data in the "Department" and "Employee" tables, demonstrating the relationship between the two tables using the foreign key constraint.

Conclusion:

Understanding the difference between primary and foreign keys is essential when working with relational databases. The primary key uniquely identifies records within a table, while the foreign key establishes relationships between tables. In our real-time example of an Employee and Department database, the primary key in the Employee table ensures unique identification of employees, while the foreign key references the primary key in the Department table, maintaining referential integrity. By grasping the distinction between primary and foreign keys, database administrators and developers can design robust database structures and establish effective relationships between tables, ensuring data integrity and consistency.