What is a Primary Key in SQL?

A Primary Key in SQL is a column or combination of columns used to uniquely identify each row in a table.

In simple words:

A primary key ensures that every record in a table is unique.

Why Primary Key is Important

Databases store thousands or even millions of records.

Without a primary key:

• Duplicate records may occur
• Data retrieval becomes difficult
• Relationships between tables become unreliable

Primary keys solve these problems.

Real-Time Example

Suppose a learning platform stores student details.

Students Table

Primary Key Example

In this table:

ID

acts as the primary key.

Reason:

• Each student has unique ID
• No duplicate IDs allowed

Primary Key Characteristics

• Must be unique
• Cannot contain NULL values
• Only one primary key per table
• Used for fast searching and relationships

Primary Key Architecture

Students Table
------------------------------------------------
| ID (PK) | Name     | Course                 |
------------------------------------------------
| 1       | Naresh   | MySQL                  |
| 2       | Rahul    | Spring Boot            |
------------------------------------------------

How to Create Primary Key

Primary key is created using:

PRIMARY KEY

SQL Syntax

CREATE TABLE students (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    course VARCHAR(100)

);

Explanation

What Happens if Duplicate Primary Key is Inserted?

Database throws an error.

Example

INSERT INTO students
VALUES (1, 'Naresh', 'MySQL');

INSERT INTO students
VALUES (1, 'Rahul', 'Spring Boot');

Error

Duplicate entry '1' for key 'PRIMARY'

Why NULL is Not Allowed

Primary key uniquely identifies records.

NULL means:

Unknown Value

Unknown values cannot uniquely identify rows.

Example

INSERT INTO students
VALUES (NULL, 'Naresh', 'MySQL');

Error

Primary key cannot be NULL

Related Learning Topics

• What is a Database?
• What is a Table in SQL?
• Creating Databases and Tables
• MySQL Constraints Primary and Foreign Keys
• Inserting and Updating Data

Primary Key vs Normal Column

How Primary Key Improves Performance

Primary keys automatically create indexes.

Indexes improve:

• Search performance
• Query speed
• Data retrieval efficiency

Example Query

SELECT * FROM students
WHERE id = 1;

This query becomes faster because:

• Primary key index is used

What is Composite Primary Key?

A composite primary key uses multiple columns together to uniquely identify a row.

Example

CREATE TABLE enrollments (

    student_id INT,

    course_id INT,

    PRIMARY KEY (student_id, course_id)

);

Why Composite Key is Used

A student can enroll in multiple courses.

Combination of:

student_id + course_id

must remain unique.

Primary Key in Real-Time Applications

Banking System

account_id

E-Commerce Platform

order_id

Learning Platform

student_id

Hospital Management

patient_id

Primary Key and Foreign Key Relationship

Primary keys are commonly referenced by foreign keys.

Relationship Example

Students Table
      |
      v
Enrollments Table

Students Table

id (PRIMARY KEY)

Enrollments Table

student_id (FOREIGN KEY)

Foreign Key Example

CREATE TABLE enrollments (

    id INT PRIMARY KEY,

    student_id INT,

    FOREIGN KEY (student_id)
    REFERENCES students(id)

);

Benefits of Primary Key

• Ensures uniqueness
• Prevents duplicate records
• Improves query performance
• Supports relationships
• Improves data integrity

Challenges with Primary Keys

• Choosing wrong key causes design issues
• Large composite keys affect performance
• Changing primary key later becomes difficult

Best Practices for Primary Keys

• Use simple numeric IDs
• Keep keys short
• Avoid frequently changing values
• Use AUTO_INCREMENT when possible

AUTO_INCREMENT Example

CREATE TABLE students (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100)

);

Benefits of AUTO_INCREMENT

• Automatically generates unique IDs
• Reduces manual errors
• Simplifies inserts

Example Insert

INSERT INTO students(name)
VALUES ('Naresh');

Database automatically generates:

id = 1

Primary Key Query Flow

Application
      |
      v
SQL Query
      |
      v
Primary Key Index
      |
      v
Fast Record Retrieval

Primary Key in Microservices

In microservices architecture:

• Each service database uses primary keys extensively

Example

Auth Service
    |
    v
user_id

Payment Service
    |
    v
payment_id

Interview Service
    |
    v
question_id

Real-Time Production Example

In an e-commerce platform:

• Each order has unique order_id
• Each payment has unique payment_id

This ensures:

• Orders are tracked correctly
• Payments are not duplicated

Professional Interview Answer

A primary key in SQL is a column or combination of columns used to uniquely identify each row in a table. Primary keys ensure data uniqueness, prevent duplicate records, and improve query performance using indexing. A primary key cannot contain NULL values and only one primary key is allowed per table. Primary keys are heavily used in relational databases to establish relationships between tables using foreign keys and maintain data integrity.

Why Interviewers Like This Answer

• Clearly explains uniqueness concept
• Includes practical SQL examples
• Covers relationships and indexing
• Shows database design understanding
• Includes real-world examples

Frequently Asked Questions

What is primary key in SQL?

A primary key uniquely identifies each row in a table.

Can primary key contain NULL?

No, primary key cannot contain NULL values.

Can a table have multiple primary keys?

No, a table can have only one primary key, but it may contain multiple columns as composite key.

Why primary key is important?

Primary keys ensure uniqueness and improve query performance.

What is composite primary key?

A composite primary key uses multiple columns together to uniquely identify rows.