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.
精彩评论