How to change a primary key in SQL to auto_increment?

Question

I have a table in MySQL that has a primary key:

mysql> desc gifts;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| giftID        | int(11)     | NO   | PRI | NULL    |       |
| name          | varchar(80) | YES  |     | NULL    |       |
| filename      | varchar(80) | YES  |     | NULL    |       |
| effectiveTime | datetime    | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

but I wanted to make it auto_increment.

The following statement failed. How can it be modified so that it can work? thanks

mysql> alter table gifts modify giftID int primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

Answer 1

Leave off the primary key attribute:

ALTER TABLE gifts MODIFY giftID INT AUTO_INCREMENT;

Certain column attributes, such as PRIMARY KEY, aren't properties of the column so much as shortcuts for other things. PRIMARY KEY columns, for example, are assembled as the PRIMARY index; futhermore, and columns in the PRIMARY index are given the NOT NULL attribute. Since the column is already in the PRIMARY index, you don't need to specify it again when you modify the column. Try SHOW CREATE TABLE gifts; to see the affects of using the PRIMARY KEY attribute.