Solved

Auto increment MySQL problems

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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 …

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now