开发者

How to change a primary key in SQL to auto_increment?

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.开发者_StackOverflow中文版

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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜