One of my colleague sysadmins wanted to set up replication for his WordPress hosting databases. Some of his customers were using rather old WP plug-ins using tables that actually didn’t have a Primary Key (PK) defined on it. And since having (a sort of) PK is one of the first requirements for setting up replication he asked me how to solve this in an easy way.

The table has only 1 column with values:

mysql> select * from x1;
 +------+
 | col1 |
 +------+
 | 1    |
 | 2    |
 | 3    |
 | 4    |
 | 1    |
 | 2    |
 | 3    |
 | 4    |
 +------+
 8 rows in set (0.00 sec)

In MySQL adding a PK can be done with the AUTO INCREMENT option so we don’t have to worry about any sequence or serial number

ALTER TABLE x1
  ADD col2 INT PRIMARY KEY auto_increment; 

Query OK, 8 rows affected (0.16 sec)
Records: 8 Duplicates: 0 Warnings: 0

As we see from the feedback, 8 rows are affected. That means in this case that every record has it’s col2 value defaulted to a next unique sequence number:

mysql> select * from x1;
+------+------+
| col1 | col2 |
+------+------+
|   1  |   1  |
|   2  |   2  |
|   3  |   3  |
|   4  |   4  |
|   1  |   5  |
|   2  |   6  |
|   3  |   7  |
|   4  |   8  |
+------+------+
8 rows in set (0.00 sec)

Col2 has now unique values for each row and a new PK is defined on the table:

mysql> desc x1;
 +-------+---------+------+-----+---------+----------------+
 | Field | Type    | Null | Key | Default | Extra          |
 +-------+---------+------+-----+---------+----------------+
 | col1; | int(11) | YES  |     | NULL    |                |
 | col2; | int(11) | NO   | PRI | NULL    | auto_increment |
 +-------+---------+------+-----+---------+----------------+
 

Bear in mind that executing this action on a huge table can take a long time so first run this action on a test environment to determine the execution time.

Adding a PK with auto increment in Oracle is described in Add primary key to existing table in Oracle.