Resolving MySQL Error 1068 – SQLSTATE: 42000 (ER_MULTIPLE_PRI_KEY): Avoiding Duplicate Primary Keys

by

When working with MySQL databases, defining the structure of your tables is crucial. However, you might encounter Error 1068, which indicates that there’s an attempt to define more than one primary key for a table. In MySQL, each table is allowed only one primary key, which can consist of single or multiple columns. This guide will help you understand why this error occurs and how to fix it.

Understanding Error 1068 (ER_MULTIPLE_PRI_KEY)

MySQL Error 1068 is raised when a table definition includes more than one primary key declaration. This can happen during initial table creation or when modifying an existing table structure. A primary key is a unique identifier for table records, and having more than one would create ambiguity in the data model.

Diagnosing the Problem

  1. Review Table Definition: Check the SQL statement used for creating or altering the table. Look for multiple PRIMARY KEY declarations.
  2. Check for Existing Primary Keys: If you’re altering a table, it may already have a primary key defined. Use the following command to check for existing keys:
SHOW INDEXES FROM table_name WHERE Key_name = 'PRIMARY';

Replace table_name with the name of the table you’re working with.

Fixing the Error

Correct Table Creation Syntax

When creating a new table, ensure you only have one PRIMARY KEY declaration. Here’s an example of an incorrect and corrected table definition:

Incorrect Table Definition:

CREATE TABLE example_table (
  id INT NOT NULL,
  PRIMARY KEY (id),
  another_id INT NOT NULL,
  PRIMARY KEY (another_id)
);

Corrected Table Definition:

CREATE TABLE example_table (
  id INT NOT NULL,
  another_id INT NOT NULL,
  PRIMARY KEY (id)
);

In the corrected version, there is only one PRIMARY KEY declaration.

Modify Existing Table Structure

If you’re altering an existing table and encounter Error 1068, you need to drop the existing primary key before adding a new one:

ALTER TABLE example_table DROP PRIMARY KEY, ADD PRIMARY KEY (new_column);

Replace example_table with your table’s name and new_column with the column you wish to set as the new primary key.

Composite Primary Key

If you need to define a primary key on multiple columns, create a composite primary key:

CREATE TABLE example_table (
  id INT NOT NULL,
  another_id INT NOT NULL,
  PRIMARY KEY (id, another_id)
);

This creates a single primary key that spans both id and another_id columns.

Conclusion

MySQL Error 1068 occurs when there’s an attempt to define more than one primary key in a table, which is not allowed in MySQL’s relational model. By carefully reviewing your table definitions and ensuring only one primary key exists, you can avoid this error. Remember to use composite keys if you need a primary key across multiple columns. With these tips, you can maintain a clear and efficient database structure that adheres to MySQL’s constraints.

Leave a Comment