What Is a Primary Key?
A primary key is a column (or a group of columns) that uniquely identifies each row in a table. It has two strict rules:
1. Every value must be unique. No two rows can share the same primary key value.
2. Every value must be non-null. A primary key can never be empty.
Think of it like an Aadhaar number, a PAN card, or an order number — one row, one unique label. Two customers can have the same name "Rahul Kumar", but their customer IDs must be different. That is what the primary key guarantees.
A table can have only one primary key, but that key can be made up of multiple columns (called a composite primary key).
What Is a Foreign Key?
A foreign key is a column in one table that points to the primary key of another table. It is how you connect related data. A foreign key's value must always match a real primary key value in the referenced table — or it must be NULL.
Think of two tables: Customers and Orders. Every order belongs to a customer. The Orders table has a CustomerId column that points to a customer's ID in the Customers table. That pointer is a foreign key.
Our Sample Schema
Let's build the classic parent-child example we'll use throughout the article.
-- Parent table
CREATE TABLE Customers (
CustomerId INT PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL
);
INSERT INTO Customers VALUES
(101, 'Anita Sharma', 'anita@example.com'),
(102, 'Rohit Verma', 'rohit@example.com'),
(103, 'Priya Das', 'priya@example.com');
-- Child table
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
Product VARCHAR(50),
Amount DECIMAL(10, 2)
);
At this moment, Orders has no relationship with Customers. Nothing stops a careless developer from inserting an order for a customer who doesn't exist. Let's see the problem.
The Problem Without a Foreign Key
INSERT INTO Orders VALUES (1, 101, 'Laptop', 55000); -- OK, customer exists
INSERT INTO Orders VALUES (2, 999, 'Monitor', 14000); -- Customer 999 doesn't exist!
SQL Server accepts both rows. Now we have an "orphan" order linked to a customer that doesn't exist. If someone asks "which customer placed order 2?", the answer is gone forever. This is exactly what referential integrity prevents.
Adding a Foreign Key
We can add a foreign key on Orders.CustomerId that points to Customers.CustomerId using ALTER TABLE.
-- First, clean up the bad row from earlier
DELETE FROM Orders WHERE OrderId = 2;
-- Then add the foreign key
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customer
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId);
Now try the bad insert again:
INSERT INTO Orders VALUES (2, 999, 'Monitor', 14000);
-- Error: The INSERT statement conflicted with the FOREIGN KEY constraint
-- "FK_Orders_Customer". The conflict occurred in database, table "Customers",
-- column "CustomerId".
SQL Server blocks it. The foreign key does its job — bad data never reaches the table.
Adding a Foreign Key at Table Creation
You can also declare the foreign key right inside CREATE TABLE, which is the cleanest approach for new tables.
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT NOT NULL,
Product VARCHAR(50),
Amount DECIMAL(10, 2),
CONSTRAINT FK_Orders_Customer
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);
Primary Key vs Foreign Key: The Big Picture
Primary Key
Foreign Key
Comparison Table
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Uniqueness required? | Yes — every value must be unique | No — duplicates are fine |
| NULL allowed? | No | Yes (if the column allows it) |
| How many per table? | Only one | As many as you need |
| Index created automatically? | Yes (clustered by default) | No — you should add one |
| Role | Identify a row | Link to another row |
Composite Primary Keys
Sometimes one column alone cannot uniquely identify a row. A classic case is a "many-to-many" link table between students and courses, where uniqueness comes from the pair of columns.
CREATE TABLE Enrollments (
StudentId INT,
CourseId INT,
EnrollDate DATETIME,
CONSTRAINT PK_Enrollments PRIMARY KEY (StudentId, CourseId)
);
Now the pair (StudentId, CourseId) is the unique identifier — one student can enroll in many courses, and one course can have many students, but the same student cannot enroll in the same course twice.
NULLs and Foreign Keys
A foreign key column can be NULL — this is useful when the relationship is optional. Example: an employee whose manager is not assigned yet.
INSERT INTO Orders (OrderId, CustomerId, Product, Amount)
VALUES (3, NULL, 'Headphones', 2500);
If the column allows NULL, this works fine. If you want every order to have a customer, mark the column NOT NULL.
NULL. It never accepts anything else.
Dropping a Foreign Key
ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Customer;
This is why naming your constraints is so important — dropping them takes only one line if you gave them a sensible name like FK_Orders_Customer.
Common Mistakes
Mistake 1: Forgetting the foreign key entirely. Without it, nothing stops orphan rows. Unless you have a very specific reason, every relationship in your schema should be protected by a foreign key.
Mistake 2: Using two different data types on each side. If Customers.CustomerId is INT and Orders.CustomerId is BIGINT, SQL Server will refuse to create the foreign key. Always keep the types identical.
Mistake 3: Making the child column NOT NULL when the relationship is optional. If not every child row needs a parent, allow NULLs. If it's required, keep it NOT NULL.
Mistake 4: Forgetting to index the foreign key column. SQL Server indexes the primary key automatically, but not the foreign key. Queries that join on the FK will be slow without an index on the child side.
Mistake 5: Confusing unique constraint with primary key. A unique constraint also enforces uniqueness, but it allows one NULL value and a table can have many. A primary key allows zero NULLs and only one per table.
Real-World Design Examples
| Relationship | Parent (PK) | Child (FK) |
|---|---|---|
| Customer places orders | Customers.CustomerId |
Orders.CustomerId |
| Order has line items | Orders.OrderId |
OrderItems.OrderId |
| Post belongs to a category | Categories.CategoryId |
Posts.CategoryId |
| Employee has a manager | Employees.EmpId |
Employees.ManagerId (self-reference!) |
The Interview Answer
"A primary key is a column or group of columns that uniquely identifies every row in a table. It must be unique and never NULL, and a table can have only one primary key. A foreign key is a column in a child table that points to the primary key of a parent table — it enforces referential integrity by allowing only values that already exist in the parent, keeping data consistent and preventing orphan rows."
Summary
Primary keys and foreign keys are the foundation of every well-designed database. A primary key uniquely identifies each row in a single table — it is always unique, never null, and there is only one per table. A foreign key lives in a child table and points to a parent table's primary key, making sure that every child row refers to a real parent.
Together, they enforce referential integrity — the guarantee that your data relationships stay consistent over time. Without them, nothing stops orphan rows, broken joins, and lost information. Always name your constraints (for example PK_Orders, FK_Orders_Customer) so you can maintain them easily, and always match data types on both sides of a foreign key.
Master this one pairing — primary key on the parent, foreign key on the child — and you'll already be writing cleaner, safer SQL Server schemas than most beginner developers.
| Concept | Key Takeaway |
|---|---|
| Primary Key | Uniquely identifies each row, never NULL, one per table |
| Foreign Key | Points to a parent PK; links two tables |
| Referential Integrity | The guarantee that every FK points to a real PK row |
| Composite PK | Primary key made of multiple columns |
| NULL in FK | Allowed for optional relationships |
| Multiple FKs | A table can have many foreign keys |
| Naming | Use PK_Table and FK_Child_Parent style |
| Data types | Must match exactly on both sides of the FK |
| Indexing | PK auto-indexed; FK should be indexed manually |
| Interview line | "PK identifies, FK links — together they enforce integrity" |