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.
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:
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).
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).
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 |
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. |
ON DELETE CASCADE, ON UPDATE CASCADE) to synchronize or purge related data seamlessly.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);
When designing foreign keys, developers must define what happens when a referenced parent record is updated or deleted:
NULL when the parent row is deleted.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
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
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
Enrollments(StudentID, CourseID) table), multiple columns are combined to form a composite 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.
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.
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.
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.
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.
Explore synchronized companion classes to strengthen your database engineering foundation:
UNIQUE constraints.
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.