Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Auto increment MySQL problems

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 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