What is a Primary Key, Foreign Key, and Composite Key?

Primary Key

A primary key uniquely identifies each record in a database table. Any individual key that does this can be called a candidate key, but only one can be chosen by database engineers as a primary key.

 

Composite Key

The composite key, which is composed of two or more attributes that collectively uniquely identify each record.

 

An example would be a list of homes on a real estate market. In a well-ordered database, there should be a primary key that uniquely identifies each record.

 

Foreign Key

if there is a key in a linked table, such as a buyer’s table that references the primary key, that will be a foreign key.

 

While a primary key and a composite key might do the same things, the primary key will consist of one column, where the composite key will consist of two or more columns.

 

The relationship between a primary key and a foreign key is quite different. The key thing to understand here is that the primary key in one database table becomes a foreign key in another database table, and vice versa.

 

A foreign key in a database table is taken from some other table and applied in order to link database records back to that foreign table.

1. Primary Key

A Primary Key is a column (or a set of columns) in a database table that uniquely identifies each row in the table. It ensures that no two rows have the same value in the primary key column(s), and it cannot contain NULL values.

Key Characteristics:

• Uniqueness: Every value in the primary key column(s) must be unique.
• Non-Null: The primary key column(s) cannot have NULL values.
• Single or Composite: A primary key can consist of a single column or multiple columns (composite key).

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- Single-column primary key
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Here, EmployeeID is the primary key, ensuring that each employee has a unique identifier.

2. Foreign Key

A Foreign Key is a column (or a set of columns) in one table that refers to the Primary Key of another table. It establishes a relationship between two tables, enforcing referential integrity.

Key Characteristics:

• Referential Integrity: Ensures that the value in the foreign key column must exist in the referenced primary key column.
• Optional or Mandatory: Foreign key columns can allow NULL values unless explicitly restricted.
• One-to-One, One-to-Many, Many-to-Many: Foreign keys are used to define relationships between tables.

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

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Here, DepartmentID in the Employees table is a foreign key that references the DepartmentID primary key in the Departments table.

3. Composite Key

A Composite Key is a combination of two or more columns in a table that together uniquely identify each row. It is used when no single column can uniquely identify a record.

Key Characteristics:

• Multiple Columns: A composite key consists of two or more columns.
• Uniqueness: The combination of values in the composite key columns must be unique.
• No Single Column Uniqueness: Individual columns in a composite key may not be unique on their own.

CREATE TABLE CourseEnrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID) -- Composite key
);

Here, the combination of StudentID and CourseID uniquely identifies each enrollment. Neither StudentID nor CourseID alone is sufficient to ensure uniqueness.

• Primary Key: Think of a Social Security Number (SSN) for individuals. Each person has a unique SSN.
• Foreign Key: Think of a company ID card that references your SSN in the government database.
• Composite Key: Think of a university enrollment system where a student's ID and course ID together uniquely identify their enrollment in a specific course.

Here's a tabular comparison of Primary Key, Foreign Key, and Composite Key:

Feature	Primary Key	Foreign Key	Composite Key
Definition	A unique identifier for a record in a table.	A key in one table that refers to the Primary Key of another table.	A combination of two or more columns used as a unique identifier for a record.
Uniqueness	Must be unique for each row.	Can have duplicate values in the referencing table.	The combination of values must be unique, but individual columns may have duplicates.
Null Values	Cannot be NULL.	Can have NULL values if not a mandatory relationship.	Cannot have NULL values in any part of the key.
Number of Keys	One per table.	Can have multiple Foreign Keys in a table.	One per table but consists of multiple columns.
Relation with Other Tables	Defines the unique identity of a row in its table.	Establishes a relationship between two tables.	Ensures uniqueness when a single column cannot do so.
Indexing	Automatically indexed.	Not automatically indexed (but can be indexed manually).	Automatically indexed if declared as a Primary Key.
Example	EmployeeID in an Employees table.	DepartmentID in an Employees table referencing DepartmentID in a Departments table.	(OrderID, ProductID) in an OrderDetails table.