MySQL Primary Key (PRIMARY KEY) Explained: How to Set, Fix Errors, and Choose the Best Design

1. Introduction

In database design, the “primary key (PRIMARY KEY)” is an essential concept.
A primary key plays a critical role in ensuring the uniqueness of each record and serves as the foundation for efficient data management and integrity.

In this article, we will explain everything from the basics of primary keys in MySQL to how to set them, resolve errors, and choose good designs. This guide aims to be useful for a wide range of users, from beginners to intermediate users.

By the time you finish reading this guide, you will be able to:

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

Now, let’s start with the fundamentals of primary keys.

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

In a database, a “primary key (PRIMARY KEY)” is one of the most important types of keys used in a table to guarantee uniqueness. A primary key functions as an identifier for data and uniquely identifies each row.

Characteristics of a Primary Key

1. Guarantees uniqueness
   Values in the column(s) set as the primary key cannot be duplicated within the table.
2. NULL values are not allowed
   A primary key must always have a value and does not allow NULL.
3. Only one per table
   Each table can have only one primary key, and you cannot define multiple primary keys. However, you can create a “composite primary key” by combining multiple columns into a single primary key.

Why You Need a Primary Key

Setting a primary key provides benefits such as:

• Ensures data integrity: Prevents duplicate records from being inserted.
• Efficient searching: Improves performance for searching, updating, and deleting data.
• Manages relationships between tables: A primary key is the reference point when using foreign keys.

For example, in a table that manages user information, setting “User ID” as the primary key allows you to uniquely identify each user.

3. How to Set a Primary Key in MySQL

In MySQL, you can easily set a primary key (PRIMARY KEY). Below, we explain with concrete examples how to set a primary key when creating a new table and how to add a primary key to an existing table.

Setting a Primary Key When Creating a New Table

To set a primary key when creating a new table, use the CREATE TABLE statement. The column used for the primary key must have a NOT NULL constraint.

Basic example

In the following example, we create a table named users and set the id column as the primary key.

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

Explanation:

• The id column uses AUTO_INCREMENT to automatically generate sequential unique values.
• PRIMARY KEY (id) specifies the id column as the primary key.

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

By setting a composite primary key, you can guarantee uniqueness using a combination of multiple columns.

Example: Defining 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)
);

Explanation:

• The combination of order_id and product_id is defined as the primary key.
• Even with the same order_id, you can register records with different product_id values.

How to Add a Primary Key to an Existing Table

To add a primary key to an existing table later, use the ALTER TABLE statement.

Example: Add a primary key on a single column

Below is an example of adding id as the primary key to an existing users table.

ALTER TABLE users ADD PRIMARY KEY (id);

Example: Add a composite primary key

The same approach works when setting a primary key using a combination of multiple columns.

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

How to Drop a Primary Key

To drop a primary key, use the ALTER TABLE statement as shown below.

ALTER TABLE users DROP PRIMARY KEY;

Note:

• Dropping a primary key also drops the index created based on it, so proceed carefully.

Using AUTO_INCREMENT

A common feature used together with primary keys is AUTO_INCREMENT. With this setting, the value automatically increases each time you insert data.

Example: Primary key with AUTO_INCREMENT

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

4. Using Composite Primary Keys

A composite primary key is a method of combining multiple columns into a single primary key to ensure data uniqueness. It is especially useful when a single column cannot guarantee uniqueness or when you need to identify data based on specific business rules.

Basic concept of a composite primary key

With a composite primary key, you can guarantee that the combination of values from two or more columns is unique. For example, in a table that manages order data, combining Order ID (order_id) and Product ID (product_id) allows you to uniquely identify each product within an order.

How to set a composite primary key

Setting it when creating a new table

Below is an example of setting a composite primary key on the orders table.

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

Explanation:

• order_id and product_id are specified together as the primary key.
• Even if order_id is the same, records with different product_id values can be inserted.

Adding a composite primary key to an existing table

To add a composite primary key to an existing table, use the ALTER TABLE statement as follows.

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

Advantages of composite primary keys

1. Ensures uniqueness

• You can maintain uniqueness by combining multiple columns.

1. Fits business rules

• For example, when managing order information, order_id alone may not guarantee uniqueness, but combining order_id and product_id can preserve data integrity.

1. Simplifies data management

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

Things to watch out for with composite primary keys

1. Performance impact

• Because composite primary keys compare multiple columns during searches and inserts, they can be more costly than single-column primary keys. This is especially important in large databases.

1. Index usage limitations

• When a composite primary key is set, the automatically generated index covers multiple columns. If you want to index individual columns, you need to create separate indexes.

1. More complex design

• Combining multiple columns can make the design more complex. Choosing appropriate columns is critical.

When you should use composite primary keys

• When uniqueness must be managed by multiple conditions
  Example: Order ID and Product ID, or Project ID and Task ID.
• When a single column cannot guarantee uniqueness
  Example: If one customer can buy multiple products, combine Customer ID and Purchase Date to manage uniqueness.

5. Differences Between Primary Keys and Indexes

Primary keys (PRIMARY KEY) and indexes (INDEX) are both important concepts in MySQL, but their roles and purposes differ. This section explains the differences between primary keys and indexes and how to use each properly.

Characteristics of a primary key (PRIMARY KEY)

1. Guarantees uniqueness

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

1. Ensures data integrity

• Setting a primary key helps maintain table consistency and data integrity.

1. An index is created automatically

• When you set a primary key, an index is automatically created, improving performance for searches and data operations.

Example of a primary key

Below is an example where the id column is set as the primary key.

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

Characteristics of an index (INDEX)

1. Improves search speed

• Indexes speed up data searches and query execution.

1. Does not guarantee uniqueness

• A regular index does not guarantee uniqueness (however, a UNIQUE index does).

1. Can be set on any column

• You can create indexes on columns other than the primary key to optimize specific queries.

Example of an index

Below is an example of adding an index to the username column.

CREATE INDEX idx_username ON users (username);

Primary key vs. index

Relationship between primary keys and indexes

• A primary key automatically has an index
  When you set a primary key, a unique index is automatically created. This means the primary key column can be searched quickly.
• You can also index non-primary-key columns
  Adding indexes to columns other than the primary key can improve performance for specific queries.

Example: Using both a primary key and an index

In the following example, a primary key and an index are set on different columns.

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)
);

Explanation:

• product_id is the primary key, guaranteeing uniqueness and disallowing NULL.
• An index is set on category_id to speed up category-based product searches.

How to decide between primary keys and indexes

1. Selecting a primary key

• Set the primary key on a column that uniquely identifies the data.
• Every table should have exactly one primary key.

1. Using indexes

• Add indexes to frequently searched columns other than the primary key.
• Example: Columns often used in queries such as username or email.

1. Notes

• Too many indexes can reduce performance for inserts and updates. Setting only the necessary indexes is recommended.

6. Important Notes About Primary Keys

Primary keys (PRIMARY KEY) play a crucial role in database design, but if they are not designed and managed correctly, performance and integrity issues can occur. This section explains important considerations and best practices for primary keys.

Notes on dropping or changing a primary key

How to drop a primary key

To drop a primary key, use the ALTER TABLE statement.

ALTER TABLE users DROP PRIMARY KEY;

Notes:

1. Dropping a primary key also drops the index created based on it.
2. If foreign key constraints depend on the primary key, the drop operation will fail. You must remove the foreign key constraints in advance.

How to change a primary key

To change an existing primary key, follow these steps:

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

Example:

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

Considerations when choosing a primary key

1. Choose the right column

• Select a column that can guarantee uniqueness for the primary key.
• Common options include:
  • A numeric type with auto-increment (AUTO_INCREMENT) (example: id)
  • A natural key (example: employee number or product code)

1. Avoid columns that change frequently

• Frequently updating the primary key value can negatively impact performance.

1. Avoid long strings

• Using long strings (example: a 200-character string type) as a primary key reduces efficiency for searches and operations.

1. Avoid columns that can contain NULL

• Because primary keys do not allow NULL values, you must choose a column that always has a value.

Common errors when setting a primary key and how to fix them

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

• Cause:
  • Occurs when you try to define multiple primary keys.
• Solution:
  • Remove 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 consistent between the primary key and the foreign key.

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

• Cause:
  • Occurs when the data for the primary key exceeds the expected type or length.
• Solution:
  • Change the primary key column’s data type appropriately or fix the inserted data.

Best practices when setting a primary key

1. Prefer a single column

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

1. Use composite primary keys appropriately

• When combining multiple columns, keep the number of columns to the minimum necessary.

1. Optimize the primary key column data type

• Choose numeric types (example: INT) or short strings (example: VARCHAR(50)) to improve performance.

1. Maintain data consistency

• To preserve uniqueness and integrity, implement appropriate input validation for primary key values.

7. Best Practices for Designing Primary Keys

In database design, properly designing a primary key (PRIMARY KEY) directly affects data consistency and performance. This section explains best practices for designing primary keys effectively.

Natural key vs. surrogate key: which should you choose?

What is a natural key?

A natural key uses existing business data as the primary key.
Examples: employee number, product code, email address.

Advantages:

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

Disadvantages:

• If it includes long strings or values that change frequently, it can impact performance.
• There is a risk of duplicate data.

What is a surrogate key?

A surrogate key is a unique identifier generated within the database.
Example: a numeric ID using AUTO_INCREMENT.

Advantages:

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

Disadvantages:

• Because the value has no human meaning, it does not help interpret the data.

Which should you choose?

• Surrogate keys are recommended: In most cases, using a surrogate key (example: a numeric ID with AUTO_INCREMENT) is best.
• When to choose a natural key: Only when business rules require it 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 searching and comparisons.
• Example:
  sql CREATE TABLE customers ( customer_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (customer_id) );

1. Keep it as small as possible

• Keeping the primary key small reduces storage usage and improves index performance.
• Using INT (4 bytes) is common, and BIGINT (8 bytes) is typically only needed for very large datasets.

1. Avoid updating primary keys

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

Designing primary keys and foreign keys

• Primary keys are often used as the reference target for foreign keys (FOREIGN KEY). Therefore, primary key uniqueness and integrity are critical when designing foreign key constraints.

Example: Designing primary keys and foreign keys

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)
);

Explanation:

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

Primary key design checklist

1. Does the primary key guarantee uniqueness?
2. Are you avoiding long strings or frequently changing columns as the primary key?
3. Have you considered whether a surrogate key is more appropriate than a natural key?
4. Is the primary key column data type appropriate (such as INT or BIGINT)?
5. If using a composite primary key, are you keeping the number of columns to the minimum?

Designing primary keys properly improves database performance and preserves data integrity. In the next section, we will answer common reader questions in “8. FAQ (Frequently Asked Questions).”

8. FAQ (Frequently Asked Questions)

Here, we answer common questions about MySQL primary keys (PRIMARY KEY). We explain points that beginners to intermediate users often wonder about in an easy-to-understand way.

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

Primary key (PRIMARY KEY):

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

Unique key (UNIQUE KEY):

• Keeps values unique per row, but multiple unique keys can be set per table.
• Allows NULL values (however, NULL is treated as a unique value).

Example:

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

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

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

If you don’t set a primary key:

1. You cannot guarantee uniqueness of data.
2. You cannot set foreign key constraints.
3. Searching and operating on table data may become inefficient.

Recommendation:

• It is recommended to set a primary key on every table.

When should you use a composite primary key?

Use a composite primary key when you need to guarantee uniqueness with a combination of multiple columns.

Example:

• 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)
);

Note:

• Because composite primary keys can make design more complex, you should consider them carefully.

Is AUTO_INCREMENT required?

It is not required, but it’s useful in these situations:

• When you want to automatically generate values for the primary key column.
• When you need a numeric primary key that guarantees uniqueness.

Example:

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?

You can directly change a primary key value, but note the following:

1. If foreign key constraints exist, related data may be affected.
2. Update costs can be high and may impact performance.

Example:

UPDATE users SET user_id = 10 WHERE user_id = 1;

How does a primary key affect performance?

Benefit:

• A primary key automatically has an index, which speeds up searching and sorting.

Note:

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

What is the best data type for a primary key?

• Numeric types (INT or BIGINT) are most recommended.
• They are compact and fast for searching.
• They can be used with auto-increment (AUTO_INCREMENT).
• If using strings, VARCHAR is recommended, but keeping it short is important.

Can you set multiple primary keys?

A table can have only one primary key. However, you can create a composite primary key by combining multiple columns.

What should you do if errors occur when dropping or changing a primary key?

Common error:

• You cannot drop or change a primary key due to foreign key constraints.

Solution:

1. Drop the foreign key constraint.
2. Change the primary key, then add the foreign key constraint again.

Example:

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

9. Conclusion

In this article, we explained MySQL primary keys (PRIMARY KEY) in detail—from the basics and setup methods to important considerations, best practices, and FAQs. A primary key is essential in database design, and setting it properly greatly impacts data uniqueness and performance.

Reviewing the importance of primary keys

1. Guarantees data uniqueness
   A primary key uniquely identifies each row in a table and prevents duplicate data.
2. Improves efficiency for searching and operations
   Because a primary key automatically has an index, searches and data operations become faster.
3. Foundation for foreign key constraints
   Setting a primary key enables integrity management using foreign key constraints.

Key design points

• Choose an appropriate column for the primary key
  It’s important to set a primary key on a column that guarantees uniqueness and does not change frequently.
• Use composite primary keys carefully
  Use composite primary keys only when necessary and avoid making the design overly complex.
• Select an appropriate data type
  Using numeric types such as INT or BIGINT helps achieve efficient performance.

Next topics to learn

Once you deepen your understanding of primary keys, learning the following topics will enable even more effective database design:

1. Setting foreign keys (FOREIGN KEY)
   Learn how to build relationships between data and maintain integrity.
2. Index optimization
   Learn techniques to improve query performance by adding indexes to non-primary-key columns.
3. Normalization and denormalization
   Learn database design fundamentals such as normalization, and consider denormalization for performance.

Final note

Designing MySQL primary keys correctly is a first step toward improving the efficiency and reliability of your entire database. Use this article as a reference to build your skills in setting and managing appropriate primary keys. With solid primary key design, database development and operations become smoother, which can improve overall productivity.

That’s the end of this article. Use it as a guide and try designing effective primary keys in real projects.