make an ID in a mysql table auto_increment (after the fact)

Question

I have a div with an image in the background, that slowly moves to the left. When it reaches the end of the image, it starts moving back to the right. It repeats this animation, over and over.

Here's the fiddle I have: http://jsfiddle.net/49H65/2/

It seems to work perfectly in Chrome and Safari, but I can't get it to work in Firefox.

Here's a snippet of the jQuery code that may be causing the issue, but I'm not 100% sure:

infiniteAnimate( $('header.nb-slider') ,{
    on:{
        'background-position-x': -slideAnimate,
        'background-position-y': 0
    },off:{
        'background-position-x': $('header.nb-slider').css('background-position-x'), //use style
        'background-position-y': $('header.nb-slider').css('background-position-y') //use style
    }
});

Any help would be much appreciated. Thanks!

Solution 1

For example, here's a table that has a primary key but is not AUTO_INCREMENT:

mysql> CREATE TABLE foo (
  id INT NOT NULL,
  PRIMARY KEY (id)
);
mysql> INSERT INTO foo VALUES (1), (2), (5);

You can MODIFY the column to redefine it with the AUTO_INCREMENT option:

mysql> ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

Verify this has taken effect:

mysql> SHOW CREATE TABLE foo;

Outputs:

CREATE TABLE foo (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

Note that you have modified the column definition in place, without requiring creating a second column and dropping the original column. The PRIMARY KEY constraint is unaffected, and you don't need to mention in in the ALTER TABLE statement.

Next you can test that an insert generates a new value:

mysql> INSERT INTO foo () VALUES (); -- yes this is legal syntax
mysql> SELECT * FROM foo;

Outputs:

+----+
| id |
+----+
|  1 | 
|  2 | 
|  5 | 
|  6 | 
+----+
4 rows in set (0.00 sec)

I tested this on MySQL 5.0.51 on Mac OS X.

I also tested with ENGINE=InnoDB and a dependent table. Modifying the id column definition does not interrupt referential integrity.

---

To respond to the error 150 you mentioned in your comment, it's probably a conflict with the foreign key constraints. My apologies, after I tested it I thought it would work. Here are a couple of links that may help to diagnose the problem:

• What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?
• http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html

Solution 2

I'm guessing that you don't need to re-increment the existing data so, why can't you just run a simple ALTER TABLE command to change the PK's attributes?

Something like:

ALTER TABLE `content` CHANGE `id` `id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT 

I've tested this code on my own MySQL database and it works but I have not tried it with any meaningful number of records. Once you've altered the row then you need to reset the increment to a number guaranteed not to interfere with any other records.

ALTER TABLE `content` auto_increment = MAX(`id`) + 1

Again, untested but I believe it will work.

Solution 3

None of the above worked for my table. I have a table with an unsigned integer as the primary key with values ranging from 0 to 31543. Currently there are over 19 thousand records. I had to modify the column to AUTO_INCREMENT (MODIFY COLUMN'id'INTEGER UNSIGNED NOT NULL AUTO_INCREMENT) and set the seed(AUTO_INCREMENT = 31544) in the same statement.

ALTER TABLE `'TableName'` MODIFY COLUMN `'id'` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 31544;

Solution 4

ALTER TABLE `foo` MODIFY COLUMN `bar_id` INT NOT NULL AUTO_INCREMENT;

or

ALTER TABLE `foo` CHANGE `bar_id` `bar_id` INT UNSIGNED NOT NULL AUTO_INCREMENT;

But none of these will work if your bar_id is a foreign key in another table: you'll be getting

an error 1068: Multiple primary key defined

To solve this, temporary disable foreign key constraint checks by

set foreign_key_checks = 0;

and after running the statements above, enable them back again.

set foreign_key_checks = 1;

View more solutions