MySQL Primary Key: Basics, Design & Error Fixes

1. Introduction

• Understand the basic concept of primary keys.
• Set primary keys and resolve related errors.
• Choose the optimal primary key for database design.

2. What is a Primary Key (PRIMARY KEY)?

Primary Key Features

1. Ensures Uniqueness The values in columns set as a primary key cannot be duplicated within the table.
2. Disallows NULL Values A primary key must always have a value and does not permit NULL.
3. Only One Per Table Each table can have only one primary key; you cannot set multiple primary keys. However, a “composite primary key” that combines multiple columns into a single primary key is possible.

Why a Primary Key Is Needed

• Ensures Data Integrity: Prevents duplicate entries of the same data.
• Efficient Queries: Improves efficiency when searching, updating, or deleting data.
• Manages Data Relationships: Serves as the reference point when using foreign keys.

3. How to Set Primary Keys in MySQL

Setting a Primary Key When Creating a New Table

Basic Example

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (id)
);

• The id column is set with AUTO_INCREMENT, which automatically generates sequential unique values.
• PRIMARY KEY (id) designates the id column as the primary key.

Setting Multiple Columns as a Primary Key (Composite Primary Key)

Example: Setting a Composite Primary Key

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

• The combination of order_id and product_id is designated as the primary key.
• You can insert rows with the same order_id as long as they have different product_id values.

How to Add a Primary Key to an Existing Table

Example: Adding a Primary Key to a Single Column

ALTER TABLE users ADD PRIMARY KEY (id);

Example: Adding a Composite Primary Key

ALTER TABLE orders ADD PRIMARY KEY (order_id, product_id);

How to Remove a Primary Key

ALTER TABLE users DROP PRIMARY KEY;

• Dropping a primary key also removes the index created based on it, so proceed with caution.

Using AUTO_INCREMENT

Example: Primary Key Using AUTO_INCREMENT

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

4. Using Composite Primary Keys

Basic Concept of Composite Primary Keys

How to Define a Composite Primary Key

Setting It When Creating a New Table

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

• The primary key is defined by combining order_id and product_id.
• Even with the same order_id, records with different product_id values can be inserted.

Adding a Composite Primary Key to an Existing Table

ALTER TABLE orders ADD PRIMARY KEY (order_id, product_id);

Benefits of Composite Primary Keys

1. Guarantees Uniqueness

• Combining multiple columns ensures uniqueness.

1. Fits Business Rules

• For instance, when managing order information, order_id alone may not guarantee uniqueness, but combining order_id with product_id maintains data integrity.

1. Simplifies Data Management

• Using a composite primary key prevents duplicate data and keeps the data structure simple.

Considerations for Composite Primary Keys

1. Performance Impact

• Composite primary keys require comparing multiple columns during searches and inserts, which can be more costly than a single-column primary key. This is especially important in large databases.

1. Index Usage Limitations

• When a composite primary key is defined, the automatically generated index covers multiple columns. If you need an index on an individual column, you must create a separate index.

1. Increased Design Complexity

• Combining multiple columns can make the schema more complex. Careful column selection is essential.

When to Use Composite Primary Keys

• When uniqueness must be managed across multiple conditions Example: order ID and product ID, or project ID and task ID, etc.
• When a single column cannot guarantee uniqueness Example: a customer purchases multiple products; combine customer ID with purchase date to manage uniqueness.

5. Differences Between Primary Keys and Indexes

Features of Primary Keys (PRIMARY KEY)

1. Guarantee of Uniqueness

• A primary key uniquely identifies each row in a table.
• It does not allow duplicates or NULL values.

1. Ensuring Data Integrity

• Setting a primary key maintains table consistency and data integrity.

1. Automatic Index Creation

• When a primary key is defined, an index is automatically created, improving the efficiency of searches and data operations.

Example of a Primary Key

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

Features of Indexes (INDEX)

1. Improved Search Speed

• Indexes speed up data retrieval and query execution.

1. Does Not Guarantee Uniqueness

• Regular indexes do not guarantee uniqueness (although unique indexes do).

1. Can Be Applied to Any Column

• Indexes can be created on columns other than the primary key to optimize specific queries.

Example of an Index

CREATE INDEX idx_username ON users (username);

Differences Between Primary Keys and Indexes

Relationship Between Primary Keys and Indexes

• Primary keys automatically have an index When a primary key is defined, a unique index is automatically created. Consequently, the primary key column can be searched quickly.
• Indexes can also be set on non-primary-key columns Creating indexes on columns other than the primary key can improve the performance of specific queries.

Example: Using Both Primary Keys and Indexes

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100),
    category_id INT,
    PRIMARY KEY (product_id),
    INDEX idx_category (category_id)
);

• product_id is the primary key, guaranteeing uniqueness and disallowing NULLs.
• category_id has an index, speeding up product searches by category.

Key Points for Choosing Between Primary Keys and Indexes

1. Primary Key Selection

• Set the column that uniquely identifies the data as the primary key.
• Each table must have exactly one primary key.

1. Utilizing Indexes

• If a non-primary-key column is frequently used in search conditions, create an index on it.
• Examples: columns like username or email that are often used in queries.

1. Considerations

• Setting too many indexes can degrade performance during data insertion and updates. It is recommended to create only the necessary indexes.

6. Primary Key Considerations

Considerations for Deleting or Modifying Primary Keys

How to Delete a Primary Key

ALTER TABLE users DROP PRIMARY KEY;

1. When a primary key is deleted, the indexes created based on it are also removed.
2. If there are foreign key constraints that depend on the primary key, the delete operation will fail. You need to drop those foreign key constraints beforehand.

How to Modify a Primary Key

1. Drop the current primary key.
2. Add the new primary key.

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (username);

Considerations for Choosing a Primary Key

1. Choose an appropriate column

• Select a column that can guarantee uniqueness for the primary key.
• Common choices：
  • Numeric type with AUTO_INCREMENT (e.g., id)
  • Natural keys (e.g., employee numbers or product codes)

1. Avoid columns that are frequently updated

• Frequently updating the value of a column used as a primary key can negatively affect performance.

1. Avoid long strings

• Using long strings (e.g., a 200-character VARCHAR) as a primary key reduces the efficiency of searches and operations.

1. Avoid columns that can contain NULL values

• Since primary keys cannot contain NULL values, you must choose a column that always has a value.

Errors When Setting Primary Keys and How to Resolve Them

1. Error: ERROR 1068 (42000): Multiple primary key defined

• Cause：
  • Occurs when attempting to define multiple primary keys.
• Solution：
  • Delete the duplicate primary key definition. sql ALTER TABLE users DROP PRIMARY KEY; ALTER TABLE users ADD PRIMARY KEY (id);

1. Error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

• Cause：
  • Occurs when a foreign key constraint does not match the primary key.
• Solution：
  • Make the data types and constraints of the primary key and foreign key match.

1. Error: ERROR 1265 (01000): Data truncated for column

• Cause：
  • Occurs when the data assigned to the primary key exceeds the expected type or length.
• Solution：
  • Adjust the data type of the primary key column appropriately, or correct the inserted data.

Best Practices for Setting Primary Keys

1. Prefer a single column

• Using a single-column primary key (e.g., a numeric type with AUTO_INCREMENT) enables a simple and efficient design.

1. Appropriate use of composite primary keys

• Combining multiple columns should be done to keep the number of columns to the minimum necessary.

1. Optimize the data type of primary key columns

• Choose numeric types (e.g., INT) or short strings (e.g., VARCHAR(50)) to improve performance.

1. Maintain data consistency

• Perform appropriate input data validation on primary key columns to maintain uniqueness and integrity.

7. Best Practices for Designing Primary Keys

Natural Key vs. Surrogate Key: Which Should You Choose?

What Is a Natural Key?

• The meaning of the data is intuitive.
• It aligns easily with existing systems and business rules.

• If it includes long strings or frequently changing values, performance can be affected.
• There is a risk of duplicate data.

What Is a Surrogate Key?

• Uniqueness is always guaranteed.
• Searches and updates are efficient.
• There is no risk of the value changing.

• Because the value has no meaning to humans, it does not help with data understanding.

• Surrogate key recommended: In most cases, using a surrogate key (e.g., a numeric ID with AUTO_INCREMENT) is the best choice.
• If you choose a natural key: Only when business rules require a natural key and data integrity is guaranteed.

Performance Optimization for Primary Keys

1. Use numeric types

• Using numeric types such as INT or BIGINT for primary keys speeds up search and comparison operations.
• Example: sql CREATE TABLE customers ( customer_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (customer_id) );

1. Keep the size minimal

• Keeping primary keys small reduces storage usage and improves index performance.
• Example: INT (4 bytes) is common, while BIGINT (8 bytes) is reserved for very large datasets.

1. Avoid updating primary keys

• Updating a primary key also updates related indexes and foreign key constraints, which can cause significant performance degradation.

Designing Primary Keys and Foreign Keys

• Primary keys are often referenced by foreign keys (FOREIGN KEY). Therefore, when designing foreign key constraints, the uniqueness and integrity of the primary key are crucial.

Example: Primary Key and Foreign Key Design

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

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

• The customer_id column in the orders table references the customer_id column in the customers table.
• If primary key integrity is not maintained, foreign key constraints can trigger errors.

Primary Key Design Checklist

1. Is the primary key column guaranteed to be unique?
2. Does the primary key avoid long strings or columns that change frequently?
3. Have you considered whether a surrogate key is more appropriate than a natural key?
4. Is the data type of the primary key column appropriate (e.g., INT, BIGINT)?
5. If using a composite primary key, have you minimized the number of columns?

8. FAQ (Frequently Asked Questions)

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

• Uniquely identifies each row.
• Only one can be defined per table.
• Does not allow NULL values.

• Ensures each row’s values are unique, and multiple unique keys can be defined per table.
• Allows NULL values (though NULLs are treated as distinct).

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

• id column is the primary key.
• email column is a unique key.

What happens if you don’t define a primary key?

1. Data uniqueness cannot be guaranteed.
2. Foreign key constraints cannot be set.
3. Table data queries and operations may become inefficient.

• It is recommended to define a primary key for every table.

When should you use a composite primary key?

• When managing order data, ensure uniqueness with the combination of order_id and product_id.

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

• Composite primary keys can complicate design, so they should be considered carefully.

Is AUTO_INCREMENT required?

• When you want the column used as a primary key to be generated automatically.
• When you need a numeric primary key that guarantees uniqueness.

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (user_id)
);

How can you change a primary key value?

1. If foreign key constraints exist, related data may be affected.
2. The update cost can be high, potentially impacting performance.

UPDATE users SET user_id = 10 WHERE user_id = 1;

What impact does a primary key have on performance?

• Primary keys automatically receive an index, speeding up data retrieval and sorting.

• If the data type or length of the primary key column is not appropriate, performance may degrade.

What is the optimal data type for a primary key?

• Numeric types (INT or BIGINT) are most recommended.
• Compact and fast for searching.
• Can be used with AUTO_INCREMENT.
• If using strings, VARCHAR is recommended, but keeping the length short is important.

Can you define multiple primary keys?

How to handle errors when deleting or modifying a primary key?

• Cannot delete or modify the primary key due to foreign key constraints.

1. Drop the foreign key constraints.
2. After changing the primary key, re-add the foreign key constraints.

ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;
ALTER TABLE orders DROP PRIMARY KEY;
ALTER TABLE orders ADD PRIMARY KEY (new_id);

9. Summary

Revisiting the Importance of Primary Keys

1. Ensures Data Uniqueness A primary key uniquely identifies each row in a table, preventing duplicate data.
2. Improves Data Retrieval and Manipulation Efficiency Because a primary key automatically receives an index, searches and data operations are accelerated.
3. Foundation for Foreign Key Constraints Setting a primary key enables the use of foreign key constraints to manage data integrity.

Key Points to Consider During Design

• Choose Appropriate Columns for the Primary Key It is important to select columns that guarantee uniqueness and are not frequently changed as the primary key.
• Use Composite Primary Keys Cautiously Only use composite primary keys when necessary, and avoid making the design overly complex.
• Select Appropriate Data Types Using numeric types such as INT or BIGINT for primary keys helps achieve efficient performance.

Future Learning Topics

1. Setting Up Foreign Keys (FOREIGN KEY) Learn how to establish relationships between data and maintain integrity.
2. Index Optimization Gain the skills to improve query performance by adding indexes to columns other than the primary key.
3. Normalization and Denormalization Consider normalization, the foundation of database design, and denormalization for performance improvements.

Conclusion