Difference Between Primary Key and Foreign Key

Quick Summary: A Primary Key is a column (or set of columns) in a table that uniquely identifies each individual tuple/record, enforcing entity integrity by strictly rejecting duplicates and NULL values. A Foreign Key is a column in a child table that references the primary key of a parent table, establishing a logical relationship between tables and enforcing referential integrity to ensure no child row references a non-existent parent.

In relational database design (RDBMS), keys are fundamental constraints used to maintain consistency, enforce business rules, and connect normalized tables. Understanding how Primary Keys and Foreign Keys work together is vital for database normalization, query optimization, and avoiding orphaned records.

Primary Key vs Foreign Key Relational Schema Diagram
Figure 11.1: Relationship between Parent Table Primary Key (PK) and Child Table Foreign Key (FK) enforcing Referential Integrity.

What is a Primary Key?

A Primary Key is a minimal set of attributes that uniquely identifies each row in a relation. In SQL, declaring a column as PRIMARY KEY implicitly attaches two strict constraints:

  • UNIQUE: Every value in the column must be distinct across the entire table.
  • NOT NULL: No row can contain an empty or unknown value in the primary key column.

By default, most database engines (such as MySQL InnoDB, Microsoft SQL Server, and Oracle) automatically build a Clustered Index on the primary key, organizing table rows physically in order of the primary key values for ultra-fast point lookups (O(log n) B-Tree search).

What is a Foreign Key?

A Foreign Key is a field or collection of fields in one table that refers to the PRIMARY KEY (or a designated UNIQUE key) of another table. The table containing the foreign key is known as the child table (or referencing table), while the table containing the referenced key is known as the parent table (or referenced table).

Foreign keys enforce Referential Integrity: the RDBMS guarantees that any value entered into the child table's foreign key column must already exist in the parent table's primary key column, or be explicitly set to NULL (if permitted).

Detailed Comparison: Primary Key vs Foreign Key

Comparison Parameter

Primary Key (PK)

Foreign Key (FK)

Core Purpose

Uniquely identifies each record within its own table.

Connects two tables together by referencing the primary key of the parent table.

Integrity Constraint

Enforces Entity Integrity (every entity must be uniquely identifiable).

Enforces Referential Integrity (no orphan records allowed).

Uniqueness

Must be strictly unique. No duplicate values are accepted.

Can contain duplicate values (e.g., many employees can work in the same department).

NULL Values Allowed

Strictly NO. Cannot accept NULL under any circumstance.

YES. Foreign key columns can accept NULL values unless explicitly declared NOT NULL.

Number of Keys per Table

Exactly one Primary Key per table (can be a composite key of multiple columns).

A table can have multiple Foreign Keys referencing different parent tables.

Automatic Indexing

Automatically generates a Clustered Index (in MySQL InnoDB, MS SQL Server).

Does not automatically create an index in many engines (manual non-clustered indexing is recommended for JOIN speed).

Deletion Impact

Deleting a parent row can be blocked or cause cascading deletions in child tables.

Deleting a child row has zero impact on the referenced parent row.

Temporary / Staging Tables

Widely used even in temporary tables to prevent duplicates.

Frequently disabled or omitted in staging tables and OLAP data warehouses for fast bulk loading.

Key Conceptual Takeaways

  • One vs Many: A relational table can have only one Primary Key, but it can contain zero, one, or dozens of Foreign Keys.
  • Relationship Direction: The Foreign Key always resides inside the child table and points upward to the parent table's Primary Key.
  • Cascade Capabilities: Foreign keys provide automatic cascade operations (ON DELETE CASCADE, ON UPDATE CASCADE) to synchronize or purge related data seamlessly.

Hands-On SQL Implementation Example

The following standard ANSI SQL script demonstrates creating a parent table (Departments) with a Primary Key and a child table (Employees) with a Foreign Key linked to that parent:

-- 1. Create Parent Table: Departments
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,              -- Primary Key: Unique and NOT NULL
    DeptName VARCHAR(50) NOT NULL,
    Location VARCHAR(100)
);

-- 2. Create Child Table: Employees
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,               -- Primary Key of Employees
    FullName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Salary DECIMAL(10, 2),
    DeptID INT,                          -- Foreign Key Column
    
    -- Define Foreign Key Constraint referencing Parent Table
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (DeptID) 
        REFERENCES Departments(DeptID)
        ON UPDATE CASCADE                -- If DeptID changes in parent, update child
        ON DELETE SET NULL               -- If Dept is deleted, set employee's DeptID to NULL
);

-- 3. Inserting valid records
INSERT INTO Departments VALUES (101, 'Engineering', 'Building A');
INSERT INTO Departments VALUES (102, 'Marketing', 'Building B');

INSERT INTO Employees VALUES (1, 'Alice Smith', 'alice@company.com', 92000, 101);
INSERT INTO Employees VALUES (2, 'Bob Jones', 'bob@company.com', 75000, 101);
INSERT INTO Employees VALUES (3, 'Charlie Ray', 'charlie@company.com', 68000, NULL); -- NULL FK allowed

-- 4. Constraint Violation Example:
-- The following statement FAILS because DeptID 999 does not exist in Departments!
-- ERROR: Cannot add or update a child row: a foreign key constraint fails
-- INSERT INTO Employees VALUES (4, 'David Miller', 'david@company.com', 80000, 999);

Referential Actions: ON DELETE and ON UPDATE

When designing foreign keys, developers must define what happens when a referenced parent record is updated or deleted:

  1. CASCADE: Automatically deletes or updates all matching rows in the child table. (e.g., Deleting an Order deletes all its OrderItems).
  2. RESTRICT / NO ACTION: The default behavior. Rejects the parent delete or update if any child rows reference it.
  3. SET NULL: Sets the foreign key column in the child table to NULL when the parent row is deleted.
  4. SET DEFAULT: Sets the foreign key column in the child table to its predefined default value.

Interactive Practice Session (MCQs)

1. Which of the following constraints is implicitly enforced on every PRIMARY KEY column?

A) CHECK and DEFAULT
B) UNIQUE and NOT NULL
C) FOREIGN KEY and INDEX
D) AUTO_INCREMENT and CASCADE

View Answer & Explanation
Correct Answer: B) UNIQUE and NOT NULL
Explanation: A Primary Key requires all values to be distinct (UNIQUE) and forbids any missing or unknown values (NOT NULL) to guarantee entity integrity.

2. Can a Foreign Key column contain duplicate values?

A) Never, every key in SQL must be completely unique
B) Yes, multiple child rows can reference the same parent row
C) Only if the parent table has fewer than 10 rows
D) Only if the column data type is VARCHAR

View Answer & Explanation
Correct Answer: B) Yes, multiple child rows can reference the same parent row
Explanation: Foreign keys represent one-to-many relationships (e.g., hundreds of employees sharing the exact same DeptID).

3. What is a "Composite Primary Key"?

A) A primary key that references an external database
B) A primary key built from two or more columns combined to guarantee row uniqueness
C) A primary key encrypted using SHA-256
D) A foreign key acting as a primary key in a NoSQL database

View Answer & Explanation
Correct Answer: B) A primary key built from two or more columns combined to guarantee row uniqueness
Explanation: When no single column is unique (such as in an Enrollments(StudentID, CourseID) table), multiple columns are combined to form a composite primary key.

Viva Voce & Technical Interview Questions

Q1: Can a Foreign Key reference a column that is NOT a Primary Key?

Answer: Yes! A Foreign Key can reference any column that has a UNIQUE constraint defined on it in the parent table. The referenced column does not strictly have to be the Primary Key, but it must guarantee uniqueness.

Q2: What is an "Orphan Record" and how do Foreign Keys prevent it?

Answer: An orphan record is a child row whose parent record was deleted or never existed (for instance, an invoice pointing to a deleted customer). Foreign keys enforce referential integrity by prohibiting insertions with non-existent parent IDs and blocking unauthorized deletions of referenced parent rows.

Q3: What is the difference between a Natural Key and a Surrogate Key?

Answer: A Natural Key is a primary key formed from existing real-world business attributes (such as SSN, passport number, or email). A Surrogate Key is an artificial, system-generated identifier with no business meaning (such as an auto-incrementing integer or UUID) created specifically for efficient indexing and joining.

Q4: Why should database developers manually index Foreign Key columns?

Answer: While RDBMS engines automatically index primary keys, many engines (including SQL Server and PostgreSQL) do not automatically build indexes on foreign key columns. Creating an index on foreign keys drastically accelerates JOIN operations and prevents table-level locks during cascade deletes.

Q5: Can a table reference itself with a Foreign Key (Self-Referencing Table)?

Answer: Yes! This is called a recursive or self-referencing foreign key. A classic example is an Employees table where a column ManagerID is a foreign key that references EmpID within the same Employees table to model organizational hierarchies.

Related Difference Classes

Explore synchronized companion classes to strengthen your database engineering foundation:

Frequently Asked Questions (FAQ)

No. A relational database table can have only one Primary Key constraint. If you require multiple uniqueness rules on a single table, you should define one Primary Key and implement the remaining constraints using UNIQUE constraints.
The RDBMS immediately rejects the query and throws an entity integrity violation error (e.g., Duplicate entry '101' for key 'PRIMARY'), leaving the database unchanged.
Technically, SQL allows you to execute JOIN operations between any two columns with matching data types even without a Foreign Key. However, without a Foreign Key constraint, the database cannot enforce referential integrity, making your data susceptible to orphan rows and corrupted relations.
Share Share on Facebook Share on Twitter Share on LinkedIn Pin on Pinterest Share on Stumbleupon Share on Tumblr Share on Reddit Share on Diggit

You may also like this!