?
Solved

Auto increment MySQL problems

Posted on 2009-04-09
5
Medium Priority
?
972 Views
Last Modified: 2012-08-13
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
Comment
Question by:billy_howard
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:burningmace
ID: 24106470
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
 
LVL 1

Author Comment

by:billy_howard
ID: 24106527
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
 
LVL 5

Accepted Solution

by:
burningmace earned 2000 total points
ID: 24106649
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
 
LVL 1

Author Comment

by:billy_howard
ID: 24106903
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
 
LVL 1

Author Closing Comment

by:billy_howard
ID: 31568478
Oh lovely, a perfect solution <-- YOUR A LIFE SAVER!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question