• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Primary Key

← Previous Next →

The MySQL Primary Key is a special constraint used to uniquely identify each record in a table. Every table should ideally have a primary key to maintain data integrity, uniqueness, and efficiency in searches.

Primary keys are essential for relational database design because they serve as the reference point for relationships between tables, such as foreign keys in child tables.

What Is a Primary Key?

A primary key is a column or a combination of columns that uniquely identifies each row in a table.

Key Points:

  • Each table can have only one primary key.

  • The primary key column(s) must be unique and NOT NULL.

  • Often used as a foreign key in other tables to establish relationships.

  • Can consist of a single column or multiple columns (composite primary key).

Syntax for Primary Key

Single Column Primary Key:

CREATE TABLE table_name (
    column_name datatype PRIMARY KEY,
    ...
);

Composite Primary Key:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CONSTRAINT pk_name PRIMARY KEY (column1, column2)
);

Explanation:

  • column_name: Column used as the primary key.

  • pk_name: Optional name for a composite primary key.

  • Composite keys combine two or more columns to ensure uniqueness.

Creating a Table with a Primary Key

Example: Create a students table with student_id as the primary key:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Explanation:

  • student_id uniquely identifies each student.

  • AUTO_INCREMENT automatically generates sequential values.

  • email is unique but not the primary key.

Composite Primary Key

A primary key can include multiple columns when uniqueness is determined by a combination of values:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

Explanation:

  • Ensures that each student can enroll in a specific course only once.

  • student_id and course_id together form the primary key.

Adding a Primary Key to an Existing Table

If a table exists without a primary key, you can add one using ALTER TABLE:

ALTER TABLE students
ADD PRIMARY KEY (student_id);

Explanation:

  • Marks student_id as the primary key.

  • Existing values must be unique and not null.

Primary Key and AUTO_INCREMENT

Using AUTO_INCREMENT with a primary key automatically generates unique identifiers for new rows:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL
);

Explanation:

  • order_id is unique for every order.

  • Useful for tables where each row needs a distinct, sequential identifier.

Primary Key vs Unique Key

Feature Primary Key Unique Key
Uniqueness Must be unique Must be unique
NULL Values Not allowed Allowed (one NULL per column)
Number per Table Only one primary key per table Multiple unique keys allowed
Purpose Identify each record uniquely Prevent duplicate values

Explanation:

  • Use a primary key for the main identifier of the table.

  • Use unique keys for additional columns that must also be unique.

Practical Use Cases

  1. Student Database: student_id as primary key to uniquely identify students.

  2. Orders Table: order_id as primary key with AUTO_INCREMENT.

  3. Composite Key Example: student_id + course_id in enrollments table.

  4. Employee Table: employee_id as primary key, ensuring no duplicates.

  5. Inventory System: product_id as primary key for unique items.

Best Practices

  1. Always Define a Primary Key: Helps maintain data integrity.

  2. Prefer Single Column Keys: Simple primary keys are easier to manage.

  3. Use AUTO_INCREMENT for Numeric Keys: Simplifies record insertion.

  4. Avoid Changing Primary Keys: Changing a key may break relationships.

  5. Composite Keys When Necessary: Only use when uniqueness depends on multiple columns.

Common Mistakes to Avoid

  1. Not Using a Primary Key: Leads to duplicate or ambiguous records.

  2. Using Nullable Columns: Primary keys must be NOT NULL.

  3. Overcomplicating Keys: Composite keys are harder to maintain.

  4. Ignoring Relationships: Primary keys should align with foreign keys in related tables.

  5. Duplicating Values: Any duplicate values will prevent primary key creation.

Summary of the Tutorial

The MySQL Primary Key is fundamental for uniquely identifying records and maintaining relational integrity.

  • Single-column or composite primary keys enforce uniqueness.

  • Often combined with AUTO_INCREMENT for automatic numbering.

  • Essential for referential integrity, indexing, and optimized queries.

  • Supports reliable and consistent database structure for all applications.

Using primary keys correctly ensures your database is organized, efficient, and error-resistant.

Practice Questions

  1. Create a table students with student_id as PRIMARY KEY and student_name as NOT NULL.

  2. Add a primary key on the course_id column of the courses table.

  3. Create a table enrollments with a composite primary key on student_id and course_id.

  4. Create an orders table with order_id as AUTO_INCREMENT PRIMARY KEY, customer_id, and order_date.

  5. Modify an existing table employees to add a primary key on employee_id.

  6. Create a departments table with department_id as PRIMARY KEY and department_name as NOT NULL.

  7. Insert multiple rows into a table with a composite primary key and test for duplicates.

  8. Create a products table with product_id as AUTO_INCREMENT PRIMARY KEY and product_name as UNIQUE.

  9. Try inserting a duplicate value into a primary key column and observe the result.

  10. Create a table library_books with book_id as PRIMARY KEY and isbn as UNIQUE NOT NULL.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top