Solved

Auto increment MySQL problems

Posted on 2009-04-09
5
933 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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

680 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