Primary Key in DBMS – Full Explanation with Example

A Primary Key is a fundamental concept in relational database management systems (RDBMS). It is used to uniquely identify each record (or row) in a database table.

What is a Primary Key?

A Primary Key is a column or a set of columns in a table that uniquely identifies each row in that table.

Key Characteristics of a Primary Key:

1. Uniqueness: Each value in the primary key column(s) must be unique.
2. Not Null: A primary key column cannot contain NULL values.
3. Single Per Table: A table can have only one primary key.
4. Immutable: The value of a primary key should not change frequently.
5. Automatic Indexing: Most database systems automatically create an index on the primary key for faster access.

📋 Example of Primary Key

Consider a table named Students:

In this table:

• student_id is the Primary Key.
• It uniquely identifies each student in the table.
• No two students can have the same student_id.
• student_id cannot be NULL.

SQL Syntax to Define a Primary Key

While creating the table:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    class VARCHAR(10)
);

Or, define primary key separately:

CREATE TABLE Students (
    student_id INT,
    name VARCHAR(100),
    age INT,
    class VARCHAR(10),
    PRIMARY KEY (student_id)
);

Composite Primary Key

A Composite Primary Key uses more than one column to uniquely identify a row.

Example:

CREATE TABLE CourseEnrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

In this case, neither student_id nor course_id alone is sufficient to identify a unique row. But together, they do.

Violations of Primary Key Rules

• Duplicate student_id: Violates uniqueness.
• If student_id is NULL: Violates not-null property.

Primary Key vs Unique Key

Summary

• A Primary Key ensures each record in a table is unique and not null.
• It can be a single column or a combination of columns.
• It is crucial for data integrity and relationships in relational databases.