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

1. Natural vs Surrogate Keys

   • Natural: Business-meaningful (SSN, email)
   • Surrogate: System-generated (auto-increment)

2. Performance Considerations

   • Integer keys perform better than string keys
   • Keep primary keys as small as possible
   • Index primary keys automatically created

3. 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

🔍 Related Concepts

• 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.