Primary Key in DBMS

In this article Primary Key in DBMS we give the information about A Primary Key is a column or set of columns in a table that uniquely identifies each row in that table.

Primary Key in DBMS

Definition:

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

• It cannot contain NULL values.
• It must hold unique values.
• Every table should have only one primary key.

Why Primary Key is Important:

• Ensures data integrity by preventing duplicate rows.
• Used to define relationships between tables (via foreign keys).
• Helps in indexing for faster searching.

Example:

Table: STUDENT

• Here, RollNo is the Primary Key because it uniquely identifies each student.

Rules for Primary Key:

1. Must be unique.
2. Must be NOT NULL.
3. One per table only.
4. Can be single column or composite key (multiple columns together).

Composite Primary Key Example:

Table: Course_Registration

• Primary Key = (StudentID, CourseID)
  ⇒ Together they uniquely identify a row.

SQL Syntax Example:

CREATE TABLE Student (

    RollNo INT PRIMARY KEY,

    Name VARCHAR(50),

    Course VARCHAR(20)

);

For composite primary key:

CREATE TABLE Course_Registration (

    StudentID INT,

    CourseID INT,

    Semester INT,

    PRIMARY KEY (StudentID, CourseID)

);

Candidate Key in DBMS

Definition:

A Candidate Key is a minimal set of attributes (columns) in a relation (table) that can uniquely identify a tuple (row).

• All Candidate Keys are unique and minimal.
• A table can have multiple candidate keys.
• One of the candidate keys becomes the Primary Key.

Key Points:

• Unique: No two rows can have the same value for a candidate key.
• Minimal: No attribute can be removed from the set without losing uniqueness.
• All Candidate Keys qualify to be the Primary Key.

Example:

Table: STUDENT

Candidate Keys:

• RollNo
• Email
• AadharNumber

All of these uniquely identify a student, so they are Candidate Keys.
You can choose one as the Primary Key (say, RollNo), and the others remain as Alternate Keys.

Difference Between Candidate Key & Primary Key

SQL Candidate Key Concept

While SQL does not directly define “CANDIDATE KEY” as a keyword, you enforce it manually using UNIQUE constraints.

CREATE TABLE STUDENT (

    RollNo INT PRIMARY KEY,

    Email VARCHAR(100) UNIQUE,

    AadharNumber CHAR(12) UNIQUE,

    Name VARCHAR(50)

);

In this case:

• RollNo is the Primary Key
• Email and AadharNumber are Candidate Keys with UNIQUE constraints

Super Key in DBMS

Definition:

A Super Key is any combination of attributes in a table that uniquely identifies each record (row) in that table.

Every Primary Key is a Super Key, but not every Super Key is a Primary Key.

Key Characteristics:

• It can contain extra attributes — not minimal.
• A table can have many super keys.
• Among all super keys, the minimal ones are called Candidate Keys.

Example:

Table: STUDENT

Possible Super Keys:

1. {RollNo}
2. {Email}
3. {RollNo, Name}
4. {RollNo, Email}
5. {RollNo, Name, Department}

All these combinations uniquely identify a student, so they are Super Keys.

Super Key vs. Candidate Key

Candidate Keys ⊂ Super Keys

Summary

• Super Key = Any unique identifier (may include unnecessary fields)
• Candidate Key = Minimal Super Key
• Primary Key = One selected Candidate Key

Visual Example

If STUDENT table has:

• RollNo (unique)
• Email (unique)

Then:

Foreign Key in DBMS

Definition:

A Foreign Key is a column (or a set of columns) in one table that refers to the Primary Key in another table.

It is used to establish a relationship between two tables.

Key Characteristics:

• Ensures referential integrity.
• The value of a foreign key must match an existing value in the referenced primary key or be NULL.
• Foreign keys can have duplicate values (unlike primary keys).

Example:

Table 1: Department

Table 2: Student

Here:

• Student.DeptID is a Foreign Key
• It refers to Department.DeptID (the Primary Key)

This ensures that each student is assigned to a valid department only.

SQL Syntax:

CREATE TABLE Department (

    DeptID VARCHAR(5) PRIMARY KEY,

    DeptName VARCHAR(100)

);

CREATE TABLE Student (

    RollNo INT PRIMARY KEY,

    Name VARCHAR(100),

    DeptID VARCHAR(5),

    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

);

Foreign Key vs Primary Key

Referential Integrity

• DBMS ensures that foreign key values must exist in the parent table.
• If you try to insert a value in the child table that doesn’t exist in the parent, it will cause an error.

What if the parent record is deleted?

Use these ON DELETE actions:

• ON DELETE CASCADE: Delete child records too
• ON DELETE SET NULL: Set foreign key to NULL in child
• ON DELETE RESTRICT: Prevent deletion of parent if children exist

Unique Key in DBMS

Definition:

A Unique Key is a constraint in a database table that ensures all values in a column (or combination of columns) are unique across the table.

Unlike a Primary Key, a Unique Key can accept a NULL value, but only one NULL (in most RDBMS like MySQL).

Key Characteristics:

Example:

Table: STUDENT

Here:

• RollNo is the Primary Key
• Email and AadharNumber are Unique Keys

Both are unique across the table but are not chosen as the Primary Key.

SQL Syntax:

CREATE TABLE STUDENT (

    RollNo INT PRIMARY KEY,

    Name VARCHAR(50),

    Email VARCHAR(100) UNIQUE,

    AadharNumber CHAR(12) UNIQUE

);

You can also name a unique constraint:

sql

CopyEdit

CREATE TABLE STUDENT (

    RollNo INT PRIMARY KEY,

    Email VARCHAR(100),

    CONSTRAINT UC_Email UNIQUE (Email)

);

Primary Key vs Unique Key

Use Case Examples:

• Email address in user table
• Username in login table
• PAN/Aadhar in citizen database

POP- Introduction to Programming Using ‘C’

DS – Data structure Using C

OOP – Object Oriented Programming 

Java Programming

DBMS – Database Management System

RDBMS – Relational Database Management System

Join Now: Data Warehousing and Data Mining