Geek asks and answers

How to insert auto_increment

If I have the following database

CREATE TABLE `my_table` (
  `year` text,
  `event` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `my_table` VALUES ('2011', 'john');
INSERT INTO `my_table` VALUES ('2011', 'dave');
INSERT INTO `my_table` VALUES ('2010', 'hany');

My question is how to add id auto_incrementso that it can be

INSERT INTO `my_table` VALUES (1, '2011', 'john');
INSERT INTO `my_table` VALUES (2, '2011', 'dave');
INSERT INTO `my_table` VALUES (3, '2010', 'hany');

I am tired of this, but could not

$q1 = "ALTER TABLE `my_table` ADD COLUMN `id` int(3) NOT NULL auto_increment";
mysql_query($q1) or die(mysql_error()." at row ".__LINE__);

and gave me an error Incorrect table definition, therefore, how to do it right.

+3
source share
1 answer

you must first add COLUMN, then INDEX, and thenAUTO_INCREMENT

ALTER TABLE  `my_table` ADD COLUMN `id` int(3) NOT NULL;
ALTER TABLE  `my_table` ADD PRIMARY KEY (`id`);
ALTER TABLE  `my_table` CHANGE  `id`  `id` INT( 3 ) NOT NULL AUTO_INCREMENT;

OR 2 queries:

ALTER TABLE my_table ADD COLUMN `id` int (3) NOT NULL;
ALTER TABLE my_table ADD PRIMARY KEY `id`(`id`), CHANGE  `id`  `id` INT( 3 ) NOT NULL AUTO_INCREMENT;
+6
source

More articles:


All Articles