MySQL Primary Key Guide: CREATE TABLE and ALTER TABLE Statements

Following the best practices of RDBMS involves the consistent usage of primary keys when designing databases to create and enforce Referential Integrity which is a major requirement for Data Quality and proper relational database design. In this tutorial, we provide background information on primary keys and their function as well as give code examples to illustrate the theory. Keep reading to learn what is a primary key, and how you can add, modify, and delete primary key in MySQL table.

What is a Primary Key in MySQL?

Primary Key (PK) comprises a column or a combination of columns whose role is to provide a unique identifier to each record in a table. The main points behind Primary Keys are the following:
– A table can have only one Primary Key but it can be a Composite Key consisting of multiple columns.
– Since it uniquely identifies each row in the table, a primary key column cannot have NULL values.
– A primary key column must contain unique values. If a Primary Key is composite, the combination of values in the primary keys columns must be unique.
– Integer datatype is the best choice for primary key columns because MySQL works faster with integers.

Why do you need a Primary Key?

Primary Keys are a cornerstone of database normalization. The main reason why we need Primary Keys is to ensure data integrity and prevent data redundancy in a database.  The main purpose of a Primary Key is to implement a relationship between two tables in a database. Relational databases must have PKs to relate a parent table to a child table since the parent table Primary Key is migrated to the child table as its Foreign Key (FK). If a table in your database doesn’t relate to any other table, it doesn’t need a primary key.

The absence of Primary Keys on a MySQL or MariaDB database indicates a very poor database design since it leads to:
– inferior performance
– renaming data columns affects other tables
– replication failures, etc.

Primary key vs unique key: What is the difference?

Both Unique and Primary Keys guarantee the uniqueness of a column or a set of the columns on which they are defined. There is an automatically defined Unique Key constraint within a Primary Key constraint. However, a table can have several Unique constraints, but only one Primary Key constraint. Unlike Primary Keys, Unique Keys accept NULL values (one per table).

Unique Keys assure that the value in the column will be unique throughout the table. In case you try to add a duplicate record, you will get an error. On the screenshot below, you can see the error notification we got in dbForge Studio for MySQL after we tried to add a duplicate record to a table.

Aspect	PRIMARY KEY	UNIQUE KEY
Uniqueness	Ensures all rows are unique.	Ensures all rows are unique.
Null Values	Does not allow NULL values.	Allows one or more NULL values.
Number Allowed	Only one PRIMARY KEY per table.	Multiple UNIQUE KEYS allowed per table.
Use Cases	Used to uniquely identify each row.	Used to enforce uniqueness for specific columns.

Difference between primary key and foreign key in MySQL

A Foreign Key comprises a column or a set of columns in a database table that serves to link two tables. Most often, a Foreign Key column in the child table is Primary Key in the parent table. Unlike Primary and Unique Keys, MySQL Foreign Keys can accept multiple NULL values. You can have more than one Foreign Key in a table. Another important difference to consider is the fact that Foreign Keys do not automatically create an index, clustered or non-clustered. You must manually create an index on foreign keys.

In the screenshot below, the table actor has a Primary Key column actor_id, the table film in its turn has a Primary Key column film_id. The columns actor_id and film_id form Foreign Keys for the film_actor table.

The screenshot represents the Database Design functionality of dbForge Studio for MySQL that helps painlessly perform various database tasks from the database structure visualization to Primary and Foreign Keys configuration.

How to create a table in MySQL with a primary key

Most often, the primary key for a table is defined in the CREATE TABLE statement. MySQL syntax for creating a table with a primary key is the following:

CREATE TABLE table_name
(
  column1 column_definition,
  column2 column_definition,
  ...

  CONSTRAINT [constraint_name] 
   PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
);

The screenshot below demonstrates a worked example of creating a table with a Primary Key in MySQL with the help of dbForge Studio for MySQL. Among other useful features, the IDE allows obtaining quick object information and syntax check instantly.

How to add a primary key to an existing table in MySQL

You can add a primary key to a table using the ALTER TABLE statement. MySQL syntax for adding a primary key to an existing table is the following:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

The screenshot below demonstrates a worked example of adding a Primary Key to the existing table in MySQL with the help of dbForge Studio for MySQL. Its Code Completion functionality eliminates errors and allows writing SQL code quickly and effectively.

How to drop a primary key in MySQL

Looking for ways how to remove primary key in MySQL? To drop it from a table, you can use the ALTER TABLE statement. The syntax to delete a primary key from a table is the following:

ALTER TABLE table_name
  DROP PRIMARY KEY;

See the screenshot that describes how to drop primary key in MySQL table with the help of dbForge Studio for MySQL. Its smart word and phrase completion help code faster and more accurately.

Managing primary keys using Visual Database Diagram

dbForge Studio for MySQL allows manipulating MySQL and MariaDB primary keys as well as foreign keys in a convenient interface without writing a single line of code. To add, delete, or change a MySQL primary key on a table:

1. Right-click the table on a diagram you want to add a primary or foreign key to.
2. From the context menu that appears, select an operation you want to perform.

3. In the Configuration window that opens, you can add, remove, or change primary or foreign keys settings.

Modifying primary keys in MySQL

In a well-designed database, the primary key is a fundamental component ensuring data integrity, uniqueness, and efficient indexing. Changing a primary key requires updating all foreign key references, leading to downtime and performance issues, so it’s not recommended. However, in some cases, changing the primary key becomes necessary. For one, the project once used a Natural Key for a Primary Key, which isn’t a good practice (these values might change with time). Or there might have been the need for standardization when one company merges with another, and their databases have different schemas. Additionally, some primary keys are poorly designed for performance (for instance, large text fields or non-sequential values are used.) If you have encountered some of these challenges, know that you still have the ability to modify primary keys, even though the process might be tricky. 

Changing an existing primary key

Here’s a practical example of how you can change primary key in MySQL from email to id in a users table.

Let’s assume that this is the table structure we intend to modify:

CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, we’re going to backup everything prior to making any changes. Also, make sure you test everything in the staging environment in order to avoid performance interruptions.

1. Let’s start with dropping the primary key that previously existed and adding a new column id BIGINT. Also, we’re going to use AUTO_INCREMENT attribute for it:

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY;

A new column (id) with the BIGINT AUTO_INCREMENT attribute will be a new primary key column. Here, the AUTO_INCREMENT attribute ensures that each new row gets a unique, sequential numeric ID starting from 1 (or another defined value). The PRIMARY KEY constraint on id makes it the new unique identifier for each row. And, since primary keys cannot be NULL, MySQL must populate this column with unique values.

As a result, we’ll get the following table structure:

CREATE TABLE users (
    email VARCHAR(255),  
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    id BIGINT AUTO_INCREMENT PRIMARY KEY  
);

As a result, email is just a regular column now, and id is a new primary key with unique values.

Removing a primary key constraint in MySQL 

Now that we know how to modify primary keys, even though this should be done only when it’s absolutely necessary, let’s overview some of the challenges that arise with these changes. 

Data migration complexity

If the primary key is referenced by foreign keys in other tables, modifying it requires updating all related tables. Thus, it won’t be possible to preserve the dependencies, and you will have to disable foreign key constraints before altering tables and then re-enable foreign key constraints once you have switched to the new primary key. 

You can do this using the following code to disable foreign key constraints:

SET FOREIGN_KEY_CHECKS = 0;

And after you have dropped the old primary key, replacing it with the new one, you can re-enable foreign key constraints:

SET FOREIGN_KEY_CHECKS = 1;

Index and foreign key rebuilding

Dropping and re-adding a primary key affects indexing and foreign keys, potentially impacting query performance. Thus, you will need to analyze query performance before and after the change. It is also highly recommended that indexes be rebuilt to reclaim space and ensure the foreign key constraints are updated.

Primary key constraints and considerations

In MySQL, when designing a database, choosing the right type of primary key is crucial for performance, scalability, and data integrity. That’s why it’s important to understand primary key constraints and learn the best practices for building tables using them. Keep reading to understand how to use composite primary keys and how auto-incrementing of primary keys works.

Composite primary keys

So, what is the composite primary key, and in which cases you should use it? First, let’s understand the logic behind the usage of composite primary keys. Imagine that you have two columns closely related in a Many-to-Many relationship, and you intend to use them together as a combination of data. For example, one column stands for courses, and the other stands for students, and you intend to use information in sync to monitor attendance. Or, you have two natural key constraints, such as “country code” and “phone number,” and you want to use this data together. How can you use this data together as a unified foreign key for other tables? The answer is simple: you need to implement composite primary keys. 

Let’s check how to define a composite primary key in MySQL for our example with student id and course id columns:

CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

As a result, this approach will let you ensure uniqueness, providing you with an easy way to implement Many-to-Many logic.

Composite primary key challenges

Foreign Key Complexity	Other tables referencing this composite key must include both columns.
Index Size	Large composite keys can slow down indexing and queries.
Auto-Increment Limitation	Composite keys do not work with AUTO_INCREMENT.

Auto-incrementing primary keys

The AUTO_INCREMENT attribute allows MySQL to generate unique numeric identifiers automatically for a primary key column. This approach ensures there’s no need for manual key assignment and provides a faster way for indesign and lookups. 

Here’s how you can define an AUTO_INCREMENT primary key:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE
);

Sometimes, you might also need to reset or change AUTO_INCREMENT values, especially when you want to prevent ID exposure for security reasons or you are looking for ways to fix AUTO_INCREMENT gaps after deletions (this was described earlier). You can easily perform it using this code:

SELECT COALESCE(MAX(id) + 1, 1) INTO @new_auto_increment FROM users;
SET @alter_statement = CONCAT('ALTER TABLE users AUTO_INCREMENT = ', @new_auto_increment);
PREPARE stmt FROM @alter_statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This SQL script dynamically resets the AUTO_INCREMENT value of the users table based on the highest existing id. It ensures that new records continue with a sequential id even if some rows were deleted. Let’s break it down to better understand how it works. 

The first part retrieves the highest id value from the users table and calculates the next available value if compared to it. If the table returns NULL, the value defaults to 1, storing the result in the @new_auto_increment.

Next, we use CONCAT(…) to build an ALTER TABLE statement dynamically, prepare the statement for execution, run it, and, after it’s applied, clean it from the memory. 

Also, in some cases, you might want to reset the default auto increment value to 1. In this case, use the following code:

TRUNCATE TABLE users;

This statement will remove all records from the users table. Note that it cannot be rolled back, so you must ensure you want to reset the auto increment. Execute it only when you won’t need the previous values anymore. 

Generally, using AUTO_INCREMENT can help you combat replication issues and fill the gaps in sequence. 

Best practices for designing primary keys

A well-designed primary key is the foundation of a fast, scalable, and reliable MySQL database. Making the right choices early on ensures efficient queries, smooth scalability, and long-term data integrity. Here’s how to structure your primary keys for maximum performance and usability:

Best Practice	Explanation
Use Integer-Based Keys for Speed	Integer (INT, BIGINT) primary keys are faster to index and search than text-based keys (VARCHAR). They consume less storage and improve query performance.
Plan for Growth with BIGINT	If your application might exceed 2 billion records, use BIGINT instead of INT to avoid overflow and costly schema changes later.
Avoid Text-Based Primary Keys	VARCHAR keys increase storage size and slow down joins, lookups, and indexing. If a text-based key is required, use CHAR(36) for UUIDs instead of VARCHAR(255).
Prefer Surrogate Keys Over Natural Keys	Using meaningful data (e.g., email, username) as a primary key can cause issues if values change. Instead, use auto-incremented numeric IDs for stability.
Minimize Primary Key Changes	Changing a primary key affects all foreign key references, leading to performance issues and downtime. Design your schema to keep primary keys stable.
Ensure Primary Keys Are Unique & Not Nullable	MySQL enforces uniqueness and disallows NULL for primary keys, ensuring data integrity. Always populate primary key columns with unique values.
Use AUTO_INCREMENT for Simplicity	For single-column numeric primary keys, AUTO_INCREMENT automates unique value generation, reducing complexity in application logic.
Index Foreign Keys for Performance	When referencing a primary key in another table, index the foreign key column to optimize lookups and joins.

Conclusion

Can you create a database table without a primary key? Yes, in MySQL, it is not required for every table to have a primary key.
Should you create a database table without a primary key? No, definitely not.

dbForge Studio for MySQL, being a universal GUI tool for MySQL and MariaDB database development, management, and administration, significantly facilitates dealing with primary key constraints.

FAQ

Can I change the primary key in MySQL after creating a table?

Yes, you can change the primary key by first dropping the existing one and then adding a new primary key constraint. Be sure to handle dependencies and ensure data integrity.

How do I add a primary key to an existing table in MySQL?

Use ALTER TABLE to add a primary key constraint:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

Ensure the column is unique and not nullable before adding the constraint.

Can a primary key in MySQL consist of multiple columns?

Yes, this is called a composite primary key. It ensures uniqueness across multiple columns. Use this approach when no single column can uniquely identify a row.

What happens if I try to insert a nullable value in a MySQL primary key column?

MySQL will reject the insertion with an error because primary key columns must be non-null and unique.

How do I update a MySQL primary key without losing data?

To do it, you should add a new column (if necessary) and populate it with unique values.

Next, you should drop the existing primary key and set the new column as the primary key. Note that after that, you should also ensure that foreign key references (if any) are updated accordingly.

How do I use dbForge Studio for MySQL to drop a primary key constraint safely?

To remove the primary key from a table, you can use the ALTER TABLE statement. The syntax to delete a primary key from a table is the following:

ALTER TABLE table_name  DROP PRIMARY KEY;

Since dbForge Studio for MySQL provides intelligent code completion, you will get suggestions on which element to drop. By choosing a certain PRIMARY KEY from the list, you will ensure you spell everything accurately and there are no mistakes or issues. 

Can I compare MySQL primary key structures across different databases using dbForge Studio?

Yes, dbForge Studio for MySQL offers a Database Comparison tool. As you use it, you can compare database data and schema, which includes primary keys, indexes, and constraints between two databases (e.g., the one on staging and the one on production).