What is the difference between a primary key and a foreign key in a relational database? — Software Engineering Interview Question #17

In a relational database, a primary key and a foreign key are two types of constraints that are used to define the relationships between tables.

**Primary Key:**

A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. It is used to enforce the entity integrity constraint, which ensures that every row in the table is unique and identifiable. A primary key is usually created using a single column, but it can also be created using a combination of columns. Some of the properties of a primary key are:

- It must contain unique values.
- It cannot contain null values.
- Only one primary key can be created per table.

For example, consider the following table called "Employee" that stores information about employees in a company:

CREATE TABLE Employee (
    employeeID int PRIMARY KEY,
    firstName varchar(50),
    lastName varchar(50),
    birthDate date,
    hireDate date
);

In this table, the column "employeeID" is the primary key.

**Foreign Key:**

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It is used to enforce the referential integrity constraint, which ensures that the values in the foreign key column(s) match the values in the primary key column(s) of the referenced table. A foreign key establishes a relationship between two tables, and it allows rows in one table to reference rows in another table. Some of the properties of a foreign key are:

- It can contain duplicate values.
- It can contain null values.
- Multiple foreign keys can be created per table.

For example, consider the following two tables called "Department" and "Employee" that store information about departments and employees in a company:

CREATE TABLE Department (
    departmentID int PRIMARY KEY,
    departmentName varchar(50)
);

CREATE TABLE Employee (
    employeeID int PRIMARY KEY,
    firstName varchar(50),
    lastName varchar(50),
    birthDate date,
    hireDate date,
    departmentID int,
    FOREIGN KEY (departmentID) REFERENCES Department(departmentID)
);

In this example, the table "Employee" has a foreign key called "departmentID" that references the primary key column "departmentID" in the table "Department". This creates a relationship between the two tables, where each row in the "Employee" table corresponds to a row in the "Department" table.