Migrating Primary Key to use AUTO_Increment

Question

I have a table where the primary key is sale_id, payment_type

mysql> describe phppos_sales_payments;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| sale_id        | int(10)       | NO   | PRI | NULL    |       |
| payment_type   | varchar(255)  | NO   | PRI | NULL    |       |
| payment_amount | decimal(15,2) | NO   |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+

I want to add a new field id that is an auto increment. The reason for this is I want to track payments of the same type individually. Is it possible to do this without losing data?

Duplicate of http://stackoverflow.com/questions/9070764/insert-auto-increment-primary-key-to-existing-mysql-database — ethrbunny, Jan 07 '13 at 17:26

Sir Rufo · Accepted Answer · 2013-01-09T23:36:24.513

4

This is how you can alter your table

ALTER TABLE `phppos_sales_payments`
  -- add a unique index based on the current primary key
  ADD UNIQUE (`sale_id`,`payment_type`), 
  -- drop the current primary key
  DROP PRIMARY KEY,
  -- add the new identity column with autoincrement
  ADD COLUMN `id` int(10) NOT NULL AUTO_INCREMENT FIRST,
  -- set primary key to the new identity column 
  ADD PRIMARY KEY (`id`);

The "magic" is to add an unique index, because foreign key must have an index and in second place add the new identity column with autoincrement and set the primary key for that column in one go.

SQL Fiddle DEMO

edited Jan 09 '13 at 23:36

answered Jan 09 '13 at 22:39

Sir Rufo

18,395
2
39
73

What does FIRST mean in the AUTO_INCREMENT? – Chris Muench Jan 11 '13 at 23:48
@ChrisMuench the new column will be in first place of your table columns. Normal would be at the last. It's just to have the PK Column on top -> cosmetic :o) – Sir Rufo Jan 11 '13 at 23:54

score 1 · Answer 2 · answered Jan 07 '13 at 17:29

1

Just keep your sale_id, payment_type key, but not as primary, then add an id INT NOT NULL AUTO_INCREMENT and make that the primary key: no loss of information and new primary key.

answered Jan 07 '13 at 17:29

DWright

9,258
4
36
53

score 1 · Answer 3 · answered Jan 10 '13 at 08:03

1

You'll need to execute a single ALTER TABLE statement to achieve this:

ALTER TABLE `table` DROP PRIMARY KEY, 
ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST, 
ADD UNIQUE sale_id_payment_type( sale_id, payment_type );

answered Jan 10 '13 at 08:03

georgepsarakis

1,927
3
20
24

score 0 · Answer 4 · answered Jan 07 '13 at 17:55

0

 ALTER TABLE `table` DROP PRIMARY KEY,  

 ALTER TABLE `table` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT FIRST
 ALTER TABLE `table` ADD PRIMARY KEY (id)

answered Jan 07 '13 at 17:55

echo_Me

37,078
5
58
78

I tried to drop the primary key, but I got #1025 - Error on rename of './pos/#sql-299_17' to './pos/phppos_sales_payments' (errno: 150) This is because sale_id is a foreign key referencing another table (FOREIGN KEY (`sale_id`) REFERENCES `phppos_sales` (`sale_id`)). Is there a way around this? – Chris Muench Jan 08 '13 at 01:36

Migrating Primary Key to use AUTO_Increment

4 Answers4