What is a primary key and a foreign key? What is the difference between them?

Primary Key — is a unique identifier of a record in a database table. It guarantees that each row in the table is uniquely identified and cannot contain NULL. Usually, the primary key is used for fast searching and ensuring data integrity.

Foreign Key — is a field (or set of fields) in one table that references the primary key of another table. The foreign key establishes a relationship between tables and maintains referential integrity, preventing the insertion of a value that does not exist in the related table.

Difference:

• The primary key is unique and identifies a record within its own table.
• The foreign key is used to link tables, referencing the primary key of another table.

Example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Here, DepartmentID in the Departments table is the primary key, and DepartmentID in the Employees table is the foreign key linking an employee to a department.