Primary Key¶

🎯 Core Concept¶

A Primary Key is a column (or set of columns) that uniquely identifies each row in a database table. It ensures data integrity and prevents duplicate records.

✅ Key Characteristics¶

Uniqueness¶

No two rows can have the same primary key value
Each row is uniquely identifiable

Non-Null¶

Primary key columns cannot contain NULL values
Every row must have a primary key value

Immutability¶

Primary key values should not change over time
Stable identification for related records

Simplicity¶

Prefer single-column keys when possible
Use meaningful, stable values (not auto-increment when business keys exist)

🏗️ Implementation Examples¶

SQL Syntax¶

-- Single Column Primary Key
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Composite Primary Key
CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

-- Adding Primary Key to existing table
ALTER TABLE Departments 
ADD PRIMARY KEY (department_id);

Auto-Increment Primary Keys¶

-- MySQL
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- PostgreSQL
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

-- Oracle
CREATE TABLE Users (
    user_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username VARCHAR(50)
);

🎓 Academic Context¶

Exam Focus Points¶

Definition: Unique identifier for table records
Properties: Not Null, Unique, Immutable
Types: Simple vs Composite keys
Viva Questions:
Why can't a primary key be NULL?
When would you use a composite key?
Difference between primary key and unique key

Mark Distribution¶

Short Answers: 2-3 marks (definition, properties)
Long Questions: 5 marks (examples, implementation)
Practical: Creating tables with primary keys

💻 Professional Context¶

Best Practices¶

Natural vs Surrogate Keys
Natural: Business-meaningful (SSN, email)
Surrogate: System-generated (auto-increment)
Performance Considerations
Integer keys perform better than string keys
Keep primary keys as small as possible
Index primary keys automatically created
Data Integrity
Foreign key references require primary keys
Cascading updates/deletes depend on primary keys
Replication and sharding require stable keys

Production Scenarios¶

User Management: User IDs for account systems
E-commerce: Product IDs for inventory
Banking: Account numbers for transactions
Healthcare: Patient IDs for medical records

Foreign Key: References primary key in another table
Unique Constraint: Similar but allows NULL values
Composite Key: Multiple columns as primary key
Surrogate Key: System-generated identifier
Natural Key: Business-meaningful identifier

This atomic content bridges academic database theory with professional database design practices.