Multiple primary key defined ошибка

Facing an error in MySQL Database Server as specified in the title of this article, “Multiple primary key defined” will be discussed in this article itself. This is the actual execution of the SQL Command performed in a MySQL Command Console :

mysql> alter table user_role add id int auto_increment primary key;
ERROR 1068 (42000): Multiple primary key defined

The table named ‘user_role’ can be described as follows :

mysql> desc user_role;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id_user | int(11) | NO   | PRI | NULL    |       |
| id_role | int(11) | NO   | PRI | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0,01 sec)

The table above has already a primary key on it. The primary key itself is a composite primary key which consists of two columns. In this context,  adding another primary key is not allowed. So, in order to add another primary key, it means the table must be altered or to make it more easier, just drop the table and recreate it with just one primary key with the rest of the column is defined as a normal field without the attribute of primary key. To prove it the column has a composite primary key, just execute the following command :

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id_user` int(11) NOT NULL,
  `id_role` int(11) NOT NULL,
  PRIMARY KEY (`id_user`,`id_role`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)

mysql> 

It is obviously shown in the above output :

PRIMARY KEY (`id_user`,`id_role`)

So, the step taken is shown below :

1. Drop the table

mysql> drop table user_role;
Query OK, 0 rows affected (0,06 sec)
mysql>

2. Recreate the table as shown below :

mysql> create table user_role(id int not null auto_increment,id_user int not null, id_role int not null, primary key(id));
Query OK, 0 rows affected (0,12 sec)

mysql> 

3. Add foreign key for the id_user column as shown below :

mysql> alter table test add constraint fk_id_user foreign key(id_user) references user(id) on update cascade on delete cascade;
Query OK, 0 rows affected (0,07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

3. Add foreign key for the id_role column as shown below :

mysql> alter table test add constraint fk_id_role foreign key(id_role) references role(id_role) on update cascade on delete cascade;
Query OK, 0 rows affected (0,08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_role;

4. Describe the table structure as shown below :

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| id_user | int(11) | NO   | MUL | NULL    |                |
| id_role | int(11) | NO   | MUL | NULL    |                |
+---------+---------+------+-----+---------+----------------+
3 rows in set (0,00 sec)

mysql>

So, adding the primary key has already been finished upon solving the error message “Multiple primary key defined”.