🔑 Keys in SQL

Last Updated: January 2026

Keys in SQL are used to identify records uniquely and create relationships between tables.

  • They play a critical role in data integrity, accuracy, and performance.
  • No duplicate records
  • Correct relationships between tables
  • Faster searching and joining
  • Data consistency

**Hinglish Tip 🗣:**Database me data sahi aur connected rakhna ho → keys zaroori hoti hain.

PRIMARY KEY (PK)

A Primary Key uniquely identifies each record in a table.

  • Must be unique
  • Cannot be NULL
  • Only one primary key per table
  • Can be single column or multiple columns
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50)
);

Meaning: student_id uniquely identifies each student

FOREIGN KEY (FK)

A Foreign Key creates a relationship between two tables.

  • It references a Primary Key of another table
  • Allows duplicate values
  • Can contain NULL

Hinglish Tip 🗣: Ek table ka key jab dusre table ke key se link ho → FOREIGN KEY.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Meaning: customer_id links orders table to customers table

UNIQUE KEY(UK)

Ensures no duplicate values, but:

  • Allows one NULL value (DB-dependent)
CREATE TABLE users (
  email VARCHAR(100) UNIQUE
);

COMPOSITE KEY

A key made of two or more columns.

  • Used when single column is not enough
  • Often used as composite primary key
PRIMARY KEY (student_id, course_id)

Meaning: student_id and course_id together make a unique key

CANDIDATE KEY

  • Columns that can become a Primary Key
  • One is chosen as Primary Key
  • Others remain candidate keys
  • A table can have one primary key But multiple candidate keys
  • email, aadhaar_no
  • One becomes primary, others stay candidates

ALTERNATE KEY

  • Candidate keys not chosen as primary key
  • email not chosen as primary → alternate key

SUPER KEY

  • Any key that can uniquely identify a row
  • Includes:
    • Primary key
    • Candidate key
    • Composite with extra columns

Natural Key

  • Comes from real-world data
  • Example: email, phone number

Surrogate Key

  • System-generated
  • Example: auto-increment ID

Example of a table with keys and constraints

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  order_amount DECIMAL(10, 2) CHECK (order_amount > 0) NOT NULL,
  order_status ENUM('Pending', 'Processing', 'Shipped', 'Delivered') DEFAULT 'Pending',
  customer_id INT UNIQUE,
  order_payment_status ENUM('Paid', 'Unpaid') DEFAULT 'Unpaid',
  order_isDelivered BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
← PreviousNext →