Understanding Primary Keys in MySQL – Detailed Notes

Course: MySQL Training

Topic: Understanding Primary Keys in MySQL

Course Website: JustAcademy – Official Website

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to Primary Keys in MySQL

A primary key is one of the most important concepts in relational database management systems. In MySQL, a primary key is a column or a combination of columns that uniquely identifies every record in a table. It ensures that each row can be distinguished from all other rows.

For example, consider a student table containing student names, email addresses, course names, and student IDs. Two students may have the same name, but each student should have a unique student ID. The student ID can be used as the primary key.

Primary keys help maintain data integrity, prevent duplicate records in key columns, and allow tables to establish relationships with other tables through foreign keys.

Example of a Student Table

In this example, student_id is a suitable primary key because every student has a different ID and each ID identifies exactly one row.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand the meaning and purpose of a primary key in MySQL.
• Identify the main characteristics of primary keys.
• Create primary keys while creating a table.
• Add a primary key to an existing table.
• Create single-column and composite primary keys.
• Understand the relationship between primary keys and auto-increment columns.
• View, modify, and remove primary key constraints.
• Identify and resolve common primary key errors.
• Apply primary keys in practical database projects.

3. What Is a Primary Key?

A primary key is a constraint that uniquely identifies each row in a database table. A primary key can consist of one column or multiple columns. MySQL does not allow duplicate primary key values or NULL values in a primary key.

3.1 Simple Definition

A primary key is a unique identifier assigned to each record in a table.

3.2 Example

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    email VARCHAR(150),
    course VARCHAR(100)
);

In this example, student_id is defined as the primary key. Every student record must have a unique, non-NULL value for this column.

3.3 Why Is a Primary Key Necessary?

• It uniquely identifies each record.
• It prevents duplicate values in the primary key column or column combination.
• It prevents NULL values in primary key columns.
• It supports relationships between tables through foreign keys.
• It helps maintain consistency and data integrity.
• It gives MySQL an index that can support efficient searches and joins.

4. Characteristics of a Primary Key

A primary key follows several important rules. Understanding these rules helps you design reliable MySQL tables.

4.1 Uniqueness

Every primary key value must uniquely identify a row. If one student has the ID 101, another student cannot use the same ID in the same table.

INSERT INTO Students (student_id, student_name, email, course)
VALUES (101, 'Rahul Sharma', '[email protected]', 'MySQL');

-- This insert will fail if student_id 101 already exists.
INSERT INTO Students (student_id, student_name, email, course)
VALUES (101, 'Priya Verma', '[email protected]', 'Python');

The second statement causes a duplicate-key error because the primary key value 101 already exists.

4.2 Primary Keys Cannot Be NULL

NULL represents a missing or unknown value in SQL. Because a primary key must identify a record, its value cannot be NULL.

INSERT INTO Students (student_id, student_name, email, course)
VALUES (NULL, 'Amit Kumar', '[email protected]', 'MySQL');

This statement fails because student_id is the primary key and cannot contain NULL.

4.3 Only One Primary Key Constraint Per Table

A table may have several columns with unique constraints, but it can have only one primary key constraint. That single primary key can contain multiple columns.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE,
    employee_name VARCHAR(100)
);

Here, employee_id is the primary key, while email has a separate unique constraint. The table still has only one primary key.

5. Primary Key vs. Unique Key

Primary keys and unique constraints both prevent duplicate values, but they serve different purposes. A primary key is the main identifier for each row, while a table can have multiple unique constraints for other columns that must not repeat.

Example

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

In this table, customer_id identifies each customer. The unique constraint on email prevents the same non-NULL email value from being stored more than once.

6. Types of Primary Keys

Primary keys are commonly categorized according to the number of columns used and the way their values are generated.

6.1 Single-Column Primary Key

A single-column primary key uses one column to identify each row. This is the simplest and most common design.

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Here, department_id uniquely identifies every department.

6.2 Composite Primary Key

A composite primary key contains two or more columns. The combination of their values must be unique, even if individual column values repeat.

Consider an enrollment table where one student can enroll in many courses and one course can have many students. The pair of student ID and course ID can identify each enrollment.

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

In this example, a student can enroll in multiple different courses, and each course can contain multiple students. However, the same student-course pair cannot appear twice.

6.3 Natural Primary Key

A natural key uses a value that already has meaning in the real world, such as a government-issued identifier or a standardized product code. A natural key can be suitable when the value is guaranteed to be unique, available, and stable.

CREATE TABLE Products (
    product_code VARCHAR(30) PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2)
);

Here, product_code identifies a product. Before using a real-world identifier, consider privacy, length, stability, and whether it may ever change.

6.4 Surrogate Primary Key

A surrogate key is a generated identifier that has no direct business meaning. Numeric IDs are commonly used because they are straightforward to manage and remain independent of changing business information.

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

Here, MySQL can generate a new value for order_id when an order is inserted without an explicitly supplied ID.

7. Creating a Primary Key During Table Creation

The most straightforward method is to define the primary key in the CREATE TABLE statement. You can declare it directly beside a column or define it as a table-level constraint.

7.1 Column-Level Primary Key

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

The expression employee_id INT PRIMARY KEY declares employee_id as the primary key.

7.2 Table-Level Primary Key

CREATE TABLE Employees (
    employee_id INT,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10,2),
    PRIMARY KEY (employee_id)
);

This version defines the primary key separately. Table-level syntax is especially useful for composite primary keys.

7.3 When Should You Use Each Method?

• Use column-level syntax when defining a simple, single-column primary key.
• Use table-level syntax when defining a composite primary key.
• Use table-level syntax when you prefer to keep all table constraints together for readability.

8. Using AUTO_INCREMENT with a Primary Key

MySQL supports AUTO_INCREMENT for integer columns. It allows MySQL to generate a new numeric value when a row is inserted without an explicit value for that column. This feature is frequently used with surrogate primary keys.

8.1 Example

CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

8.2 Insert Records

INSERT INTO Customers (customer_name, email)
VALUES ('Rahul Sharma', '[email protected]');

INSERT INTO Customers (customer_name, email)
VALUES ('Priya Verma', '[email protected]');

INSERT INTO Customers (customer_name, email)
VALUES ('Amit Kumar', '[email protected]');

8.3 View the Records

SELECT * FROM Customers;

Assuming this is a new table and no explicit IDs have been inserted, the generated IDs will typically start at 1 and increase for subsequent rows. The exact sequence depends on the table's existing state and configuration.

8.4 Important Points About AUTO_INCREMENT

• It is commonly used with integer primary keys.
• It is not a replacement for the primary key constraint; uniqueness is enforced by the key.
• Generated values may contain gaps after deletions, failed insertions, or other operations.
• Do not assume IDs are always consecutive or that a deleted ID will be reused.
• An explicit value can be inserted when permitted, so applications should not rely on every ID being generated automatically.

9. Adding a Primary Key to an Existing Table

If a table was created without a primary key, you can add one using ALTER TABLE. Before doing so, verify that the selected column contains no duplicate values and no NULL values.

9.1 Example Table Without a Primary Key

CREATE TABLE Staff (
    staff_id INT,
    staff_name VARCHAR(100),
    department VARCHAR(100)
);

9.2 Add a Primary Key

ALTER TABLE Staff
ADD PRIMARY KEY (staff_id);

The statement adds a primary key to staff_id, provided the existing data satisfies the primary key requirements.

9.3 Check Existing Data Before Adding the Key

Find duplicate non-NULL values:

SELECT staff_id, COUNT(*) AS total
FROM Staff
GROUP BY staff_id
HAVING COUNT(*) > 1;

Find rows with missing IDs:

SELECT *
FROM Staff
WHERE staff_id IS NULL;

Resolve duplicate or NULL values before adding the primary key. Do not assign arbitrary replacement IDs without checking the identity of each record and the application's relationships.

10. Viewing Primary Key Information

MySQL provides several ways to inspect a table's structure and confirm which column or columns form its primary key.

10.1 Using DESCRIBE

DESCRIBE Employees;

The output shows column names, data types, nullability, key information, default values, and other column properties. The PRI marker in the key information indicates a primary key column.

10.2 Using SHOW CREATE TABLE

SHOW CREATE TABLE Employees;

This statement displays the SQL definition of the table, including the primary key constraint.

10.3 Using SHOW INDEX

SHOW INDEX FROM Employees;

This command displays indexes defined on the table. The primary key is represented by an index whose name is typically PRIMARY.

10.4 Using INFORMATION_SCHEMA

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'Employees'
  AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION;

This query returns the primary key columns for the specified table in the currently selected database. For a composite primary key, it returns each column in key order.

11. Removing a Primary Key

In some situations, a database design may need to change. MySQL allows a primary key to be removed with ALTER TABLE. However, removing a primary key should be done carefully because other tables may depend on it through foreign key relationships.

11.1 Syntax

ALTER TABLE table_name
DROP PRIMARY KEY;

11.2 Example

ALTER TABLE Employees
DROP PRIMARY KEY;

This removes the primary key constraint from the Employees table. It does not automatically delete the employee_id column or its data.

11.3 Important Considerations

• Check for foreign key dependencies before removing a primary key.
• Dropping the primary key removes the uniqueness and non-NULL enforcement provided by that constraint.
• Some table designs or storage configurations may require additional index changes.
• If the primary key is needed, consider adding a replacement key before changing the existing design.
• Test schema changes in a development or staging environment first.

12. Changing a Primary Key

Changing a primary key generally involves dropping the old primary key and defining a new one. This must be planned carefully, particularly when the old key is referenced by foreign keys.

12.1 Example Scenario

Suppose a table currently uses employee_id as its primary key, but the database design needs to use a different column. First verify that the new column has no NULL or duplicate values. Then review dependent foreign keys and plan the required schema changes.

-- Example only: check whether the proposed key has duplicates.
SELECT proposed_key, COUNT(*) AS total
FROM Employees
GROUP BY proposed_key
HAVING COUNT(*) > 1;

-- Check for NULL values.
SELECT *
FROM Employees
WHERE proposed_key IS NULL;

proposed_key is a placeholder and must be replaced with the actual column name. Do not run these statements unchanged unless a column with that name exists.

12.2 General Process

1. Back up the database or take an appropriate recovery point.
2. Identify all tables and applications that depend on the existing key.
3. Validate the new key column or column combination.
4. Plan updates to foreign key constraints and related application code.
5. Apply the schema change in a controlled environment.
6. Test inserts, updates, joins, and related workflows.
7. Deploy the change only after confirming data integrity.

13. Primary Keys and Foreign Keys

A primary key uniquely identifies a row in its own table. A foreign key is a constraint that references a key in another table, helping maintain referential integrity between related records.

13.1 Example

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE Employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES Departments(department_id)
);

In this example:

• Departments.department_id is the primary key of the Departments table.
• Employees.employee_id is the primary key of the Employees table.
• Employees.department_id is a foreign key referencing the department table.
• Multiple employees can reference the same department.
• With the default restrictive behavior, a department cannot be deleted while referenced by employees unless the dependent rows are handled first.

13.2 Insert Related Records

INSERT INTO Departments (department_id, department_name)
VALUES
(10, 'IT'),
(20, 'Human Resources');

INSERT INTO Employees (employee_name, department_id)
VALUES
('Rahul Sharma', 10),
('Priya Verma', 20),
('Amit Kumar', 10);

13.3 Retrieve Related Data

SELECT
    e.employee_id,
    e.employee_name,
    d.department_name
FROM Employees AS e
INNER JOIN Departments AS d
    ON e.department_id = d.department_id;

The join connects employees to their departments using the foreign key relationship. Primary keys are therefore important for reliable relationships and joins across relational tables.

14. Choosing a Good Primary Key

Choosing the right primary key is an important part of database design. A poor choice can make the database harder to maintain and may create problems when business information changes.

14.1 Prefer Stable Values

A primary key should ideally remain unchanged throughout the record's lifetime. Names, phone numbers, addresses, and email addresses may change, so they are often poor choices for a permanent identifier.

14.2 Keep Keys Simple When Practical

A single-column surrogate key can simplify foreign key relationships and joins. Composite keys are useful when a combination naturally identifies a record, but they may make dependent tables more complicated if many columns must be repeated.

14.3 Use Appropriate Data Types

14.4 Avoid Unnecessary Key Changes

Changing primary key values can affect foreign keys, application references, reports, and integrations. Use a stable key whenever possible and update dependent references carefully if a change is unavoidable.

15. Common Primary Key Errors and Their Solutions

Primary key errors often occur when inserting duplicate values, inserting NULL, adding a key to existing data, or changing a table that has dependent relationships.

15.1 Fixing Duplicate Values

To find duplicate IDs before adding a primary key, use:

SELECT employee_id, COUNT(*) AS total
FROM Employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

If the table already has a primary key, MySQL normally prevents duplicate values in that key. This query is especially useful when auditing legacy data or checking a proposed replacement key.

15.2 Fixing NULL Values

SELECT *
FROM Employees
WHERE employee_id IS NULL;

Review the returned records and determine the correct identifiers. Avoid assigning the same arbitrary value to multiple records, because that would create another uniqueness problem.

15.3 Understanding Duplicate-Key Errors

INSERT INTO Employees
(employee_id, employee_name, department, salary)
VALUES
(1, 'Neha Patel', 'IT', 45000);

If employee ID 1 already exists, the insert fails. Check the current data first:

SELECT *
FROM Employees
WHERE employee_id = 1;

Choose an appropriate insert or update operation based on the intended business action. Do not update an existing record merely to suppress an error if it belongs to another person.

16. Practical Example: Creating a Student Database

This practical example demonstrates how to create a table with a primary key, insert records, retrieve them, and test the uniqueness constraint.

Step 1: Create a Database

CREATE DATABASE TrainingDB;
USE TrainingDB;

Step 2: Create a Students Table

CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    course VARCHAR(100) NOT NULL,
    enrollment_date DATE
);

Step 3: Insert Student Records

INSERT INTO Students
(student_name, email, course, enrollment_date)
VALUES
('Rahul Sharma', '[email protected]', 'MySQL', '2026-01-10'),
('Priya Verma', '[email protected]', 'Python', '2026-01-12'),
('Amit Kumar', '[email protected]', 'Data Analytics', '2026-01-15');

Step 4: Retrieve All Students

SELECT * FROM Students;

Step 5: Find a Student by Primary Key

SELECT *
FROM Students
WHERE student_id = 2;

This query retrieves the student whose primary key value is 2, assuming that record exists.

Step 6: Test Duplicate Primary Key Behavior

INSERT INTO Students
(student_id, student_name, email, course, enrollment_date)
VALUES
(1, 'Another Student', '[email protected]', 'MySQL', '2026-02-01');

If student ID 1 already exists, MySQL rejects the insert because the primary key value is duplicated. This confirms that the constraint protects the table from duplicate identifiers.

Step 7: Inspect the Table Definition

SHOW CREATE TABLE Students;
DESCRIBE Students;
SHOW INDEX FROM Students;

These commands help verify the primary key and review the table's structure and indexes.

17. Practical Example: Composite Primary Key in an Enrollment System

A composite primary key is useful when a record represents a relationship between two entities. In a course enrollment system, the same student may take several courses, and each course may have many students.

Step 1: Create the Enrollment Table

CREATE TABLE CourseEnrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    status VARCHAR(30) DEFAULT 'Active',
    PRIMARY KEY (student_id, course_id)
);

Step 2: Insert Enrollment Records

INSERT INTO CourseEnrollments
(student_id, course_id, enrollment_date, status)
VALUES
(101, 201, '2026-02-01', 'Active'),
(101, 202, '2026-02-02', 'Active'),
(102, 201, '2026-02-03', 'Active');

Step 3: Attempt a Duplicate Combination

INSERT INTO CourseEnrollments
(student_id, course_id, enrollment_date, status)
VALUES
(101, 201, '2026-02-10', 'Active');

The final statement fails because the combination of student_id = 101 and course_id = 201 already exists. The date and status do not make the composite primary key unique because they are not part of that key.

Step 4: Retrieve a Student's Enrollments

SELECT *
FROM CourseEnrollments
WHERE student_id = 101;

This query returns the enrollment records for student 101. Because student_id is the first column in the composite primary key, the primary key index can help support lookups that filter by that column.

18. Primary Key Indexing and Performance

In MySQL, the primary key is backed by a unique index. For InnoDB tables, which are commonly used in MySQL, the primary key is the clustered index. The table's row data is organized around the primary key, and secondary indexes use the primary key value to identify rows.

18.1 Why Primary Key Indexing Matters

• It supports efficient lookups using primary key values.
• It helps MySQL enforce uniqueness.
• It supports relationships and joins with foreign key columns.
• It influences the structure and storage of InnoDB tables.

18.2 Example Query

SELECT *
FROM Students
WHERE student_id = 2;

Because student_id is the primary key, MySQL can use its primary key index to locate the matching row efficiently. Actual performance depends on table size, query structure, and the execution plan.

18.3 Primary Key Design and Performance

• Use a compact key when it meets the application's requirements.
• Avoid unnecessarily long primary key values, especially in tables with many secondary indexes.
• Choose a stable key to reduce the cost and complexity of key changes.
• For composite keys, carefully consider the column order based on the expected access patterns.
• Use EXPLAIN to examine how MySQL plans to execute important queries.

19. Best Practices for Primary Keys in MySQL

1. Define a primary key for most application tables: Each table that represents distinct records should usually have a reliable way to identify each row.
2. Choose unique values: Ensure each record has a distinct identifier.
3. Prefer stable identifiers: Avoid using values that frequently change, such as names or phone numbers.
4. Use AUTO_INCREMENT when appropriate: It is a convenient choice for numeric surrogate keys.
5. Use composite keys when they represent a real uniqueness rule: They are useful for relationship tables and other records identified by a combination of columns.
6. Keep data types appropriate: Select a type that provides enough capacity without unnecessary size.
7. Plan foreign key relationships: Check dependent tables before modifying or dropping a primary key.
8. Validate existing data: Check for duplicate and NULL values before adding a primary key to an existing table.
9. Do not use a primary key as a business sequence: Generated IDs may have gaps and should not be assumed to be consecutive.
10. Test schema changes: Validate structural changes in a safe environment before applying them to production.
11. Document key decisions: Explain why a natural, surrogate, or composite key was selected.
12. Back up important data: Have an appropriate recovery plan before making destructive schema changes.

20. Common Mistakes to Avoid

• Creating a table without considering how individual records will be identified.
• Assuming that a person's name is unique.
• Using an email address as a permanent identifier without considering that it may change.
• Attempting to create a primary key on a column containing duplicate values.
• Attempting to create a primary key on a column containing NULL values.
• Declaring multiple separate primary key constraints in one table.
• Confusing a primary key with a foreign key.
• Assuming AUTO_INCREMENT values will never contain gaps.
• Dropping a primary key without checking foreign key dependencies.
• Using a composite primary key without understanding how its column combination enforces uniqueness.

21. Practice Exercises

Complete the following exercises to strengthen your understanding of primary keys.

Exercise 1: Create a Products Table

Create a table named Products with the following columns:

• product_id – integer primary key with AUTO_INCREMENT.
• product_name – VARCHAR(150), required.
• price – DECIMAL(10,2), required.
• stock_quantity – integer.

Expected solution:

CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0
);

Exercise 2: Insert Product Records

Insert at least three products without specifying product_id. Then retrieve all products and inspect the generated identifiers.

INSERT INTO Products (product_name, price, stock_quantity)
VALUES
('Keyboard', 799.00, 25),
('Mouse', 399.00, 40),
('Monitor', 8999.00, 10);

SELECT * FROM Products;

Exercise 3: Create a Composite Primary Key

Create a table named OrderItems using order_id and product_id as a composite primary key. Add a quantity column and test whether duplicate combinations are rejected.

CREATE TABLE OrderItems (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

Exercise 4: Inspect a Primary Key

Use DESCRIBE, SHOW CREATE TABLE, and SHOW INDEX to identify the primary key of your Products table.

Exercise 5: Diagnose Existing Data

Create a test table without a primary key, insert duplicate IDs and NULL values, and run queries to identify the problematic records. Explain why the primary key cannot be added until the data has been corrected.

22. Interview Questions and Answers

Q1. What is a primary key in MySQL?

Answer: A primary key is a constraint that uniquely identifies every row in a table. It does not allow duplicate or NULL values in its key columns.

Q2. How many primary keys can a table have?

Answer: A table can have only one primary key constraint. That constraint can contain one column or multiple columns.

Q3. Can a primary key contain NULL?

Answer: No. Primary key columns cannot contain NULL values.

Q4. What is a composite primary key?

Answer: A composite primary key uses two or more columns together to uniquely identify a row. The combination must be unique.

Q5. What is the difference between a primary key and a unique key?

Answer: A table has one primary key constraint, but it can have multiple unique constraints. Primary key columns cannot be NULL, while a single-column UNIQUE index generally permits multiple NULL values in MySQL.

Q6. What is AUTO_INCREMENT?

Answer: AUTO_INCREMENT allows MySQL to generate numeric values for a column when rows are inserted without an explicit value. It is commonly used with integer primary keys.

Q7. Can a primary key be added to an existing table?

Answer: Yes. You can use ALTER TABLE table_name ADD PRIMARY KEY (column_name), provided the existing data satisfies the primary key requirements.

Q8. How can you check a table's primary key?

Answer: Use DESCRIBE table_name, SHOW CREATE TABLE table_name, or SHOW INDEX FROM table_name.

Q9. Can a primary key be removed?

Answer: Yes. The statement ALTER TABLE table_name DROP PRIMARY KEY removes the primary key constraint, subject to relevant schema dependencies and restrictions.

Q10. Why are primary keys important for foreign keys?

Answer: Primary keys provide a reliable way to identify records that other tables can reference. Foreign key constraints help maintain valid relationships between related records.

Q11. What is a surrogate key?

Answer: A surrogate key is an artificial identifier, such as an auto-generated integer, that is used to identify a record independently of business data.

Q12. What happens when a duplicate primary key value is inserted?

Answer: MySQL rejects the operation with a duplicate-key error because the primary key must uniquely identify each row.

23. Quick Revision Checklist

• A primary key uniquely identifies every record in a table.
• Primary key values must be unique and cannot be NULL.
• A table can have only one primary key constraint.
• A primary key can contain one column or multiple columns.
• A composite primary key enforces uniqueness on the combination of its columns.
• AUTO_INCREMENT is commonly used with numeric surrogate primary keys.
• ALTER TABLE can be used to add or drop a primary key.
• DESCRIBE, SHOW CREATE TABLE, and SHOW INDEX help inspect primary key information.
• Primary keys support reliable joins and foreign key relationships.
• Validate existing data before adding a primary key to an existing table.
• Choose stable identifiers and consider the effects of schema changes on dependent tables.

24. Conclusion

Primary keys are a fundamental part of MySQL database design. They ensure that each record has a unique, non-NULL identifier, help maintain data integrity, and support relationships between tables. MySQL allows developers to define single-column and composite primary keys, use AUTO_INCREMENT for generated identifiers, and inspect or modify key constraints through SQL commands.

When designing a database, select a primary key that is unique, stable, and appropriate for the data. Validate existing records before adding constraints, understand the difference between primary keys and unique keys, and review foreign key dependencies before changing a key. Practicing these concepts with student, employee, product, and enrollment tables will build a strong foundation for more advanced MySQL topics.

Learn More with JustAcademy

Explore the official JustAcademy website for training resources and courses.

To learn more about database concepts and SQL queries, visit the MySQL Training Course.

You can also register for a course demo to explore the training program.