Introduction
Every application, whether a small school system or a large-scale enterprise product, stores data somewhere. Most business applications rely on SQL Server because it provides structure, consistency, performance, and reliability.
Before writing advanced queries or optimizing performance, every developer must clearly understand the fundamentals:
What are tables?
Why do we need primary keys?
How do foreign keys maintain relationships?
What is data normalization and why does it matter?
This article explains these concepts in a practical, beginner-to-intermediate format using simple examples and real-world scenarios. The language is easy to understand, yet the explanations are accurate enough for production concepts.
Real-World Context
Imagine we are building an application for a car service company. We need to store:
This is where SQL Server helps by storing data in a structured and relational form.
What Is a Table?
A table is a structured container that stores data in rows and columns, similar to an Excel sheet but with strict rules.
Example Table: Customer
| CustomerId | FullName | PhoneNumber | City |
|---|
| 1 | Rohan Sharma | 9876543210 | Mumbai |
| 2 | Priya Patel | 8877665544 | Ahmedabad |
| 3 | Vijay Kumar | 9988221100 | Bengaluru |
Tables represent entities, meaning things we store data about.
What Is a Primary Key?
A Primary Key (PK) uniquely identifies each row. Without it:
Data becomes unmanageable.
You cannot correctly reference the record.
Duplicate data may appear.
In the example above, CustomerId is the Primary Key.
Key Properties of a Primary Key
| Property | Meaning |
|---|
| Must be unique | Two rows cannot have the same key |
| Cannot be NULL | Every record must have a key |
| Usually numeric | Auto-increment values (IDENTITY) are common |
Example: Creating a Table with Primary Key
CREATE TABLE Customer (
CustomerId INT IDENTITY(1,1) PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(15),
City NVARCHAR(50)
);
IDENTITY(1,1) tells SQL Server to auto-generate IDs starting from 1.
What Is a Foreign Key?
A Foreign Key (FK) links one table to another. It enforces a relationship and prevents invalid data.
Example: A customer can have multiple cars.
Table: Car
| CarId | CustomerId | Brand | Model |
|---|
| 1 | 1 | Honda | City |
| 2 | 1 | Maruti | Swift |
| 3 | 2 | Tata | Nexon |
Here:
Creating a Table with Foreign Key
CREATE TABLE Car (
CarId INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT NOT NULL,
Brand NVARCHAR(50),
Model NVARCHAR(50),
FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId)
);
This prevents inserting a car without a valid customer record.
Why Foreign Keys Matter
Without foreign keys:
Foreign keys preserve referential integrity, meaning relationships between tables remain valid.
Database Normalization
Normalization ensures data is structured logically and without unnecessary duplication.
Example of poor design
| FullName | Phone | City | CarModel | ServiceDate |
|---|
Problems
Customer data repeats for every service.
Updating details requires modifying multiple rows.
Data becomes inconsistent.
Better
| Customer | Car | ServiceRecord |
Each with proper keys and relationships.
Example Relationship Diagram
Customer (PK: CustomerId)
|
| 1-to-many
|
Car (PK: CarId, FK: CustomerId)
|
| 1-to-many
|
ServiceRecord (PK: ServiceId, FK: CarId)
This models real-world structure cleanly.
Common Mistakes Beginners Make
| Mistake | Why It Is a Problem |
|---|
| No PK column | Cannot uniquely identify records |
| Using text instead of numeric PK | Slow joins and indexing |
| Missing FK constraints | Leads to orphan data |
| Storing repeated fields | Violates normalization |
Best Practices
Use INT IDENTITY for primary keys.
Use meaningless keys instead of natural keys (like phone numbers).
Always define foreign key constraints.
Use proper data types.
Quick Case Study
A logistics startup initially stored shipment details without relationships. After two years:
After restructuring using proper PK and FK rules:
Summary
Understanding tables, primary keys, and foreign keys is the foundation of SQL Server development. These concepts ensure:
Before building complex reports, stored procedures, or APIs, ensure your database schema follows these basic principles.