• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 972
  • Last Modified:

Auto increment MySQL problems

Hi Experts,

I am importing a database from MS ACCESS using the Bullzip database importer.

I am adding various fields with a data dumping PHP script, and one of them is an auto increment primary field called BIDREP.

For some reason when I create this field, and asign it to be an auto increment, it dosen't actually asign an auto incremented number to each row in the database.

It simply assigns each row the number 1.

So I need to know what I am doing wrong, to make it not assign an auto increment number into the database. of existing businesses.

The SQL that adds the BIDREP is below.. A quick thought, is if I set AUTO_INCREMENT=555 when I insert the bidrep field, would that work? I don't want to try it though because the database import if I screw it up takes well over 30 minutes.

ALTER TABLE `business` ADD COLUMN `BIDREP` INTEGER UNSIGNED NOT NULL AFTER `BLURB`,
	 ADD COLUMN `VID` INTEGER UNSIGNED ZEROFILL NOT NULL AFTER `BIDREP`,
	 ADD COLUMN `LIVE` INTEGER UNSIGNED NOT NULL DEFAULT 1 AFTER `VID`

Open in new window

0
billy_howard
Asked:
billy_howard
  • 3
  • 2
1 Solution
 
burningmaceCommented:
In your SQL you've not specified auto increment at all.

If you've got a PK in your table...
ALTER TABLE `mytable` DROP PRIMARY KEY

The following works for me for tables with content.
ALTER TABLE `mytable` ADD COLUMN `BIDREP` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL AFTER `BLURB`
0
 
billy_howardAuthor Commented:
Sorry, me being abit of a brainless twirp, pasted only half of it..

This next code is run just after the one above.

I do it in 2 seperate lines because I just get a million errors when attempting to auto inc a column any other way.
ALTER TABLE `business` MODIFY COLUMN `BIDREP` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`BUSINESS`, `VENUE`, `BIDREP`);

Open in new window

0
 
burningmaceCommented:
Try dropping the primary key before altering the table and using ALTER TABLE `mytable` ADD COLUMN `BIDREP` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL AFTER `BLURB` instead of a MODIFY COLUMN later on.
0
 
billy_howardAuthor Commented:
Brilliant that worked,

I first dropped the primary key,

then added the VID field as an auto inc primary key

then added the other primary keys.

Brilliant, cheers!
0
 
billy_howardAuthor Commented:
Oh lovely, a perfect solution <-- YOUR A LIFE SAVER!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now