How to change a primary key in SQL to auto_increment?
alter-table, auto-increment, mysql, primary-key, sql
Solution
Leave off the `primary key` attribute:
ALTER TABLE gifts MODIFY giftID INT AUTO_INCREMENT;
Certain column attributes, such as `PRIMARY KEY`, aren't exactly properties of the column so much as shortcuts for other things. A column marked `PRIMARY KEY`, for example, is placed in the `PRIMARY` index. Futhermore, all columns in the `PRIMARY` index are given the `NOT NULL` attribute. (Aside: to have a multi-column primary key, you must use a separate constraint clause rather than multiple `PRIMARY KEY` column attributes.) 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.
Problem
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 ```