I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code.
Can I turn those into Auto_incrementers now?
Wow, very nice, thanks a ton. It worked without a hitch on one of my tables. But a second table, i'm getting this error...Error on rename of '.\DBNAME#sql-6c8_62259c' to '.\DBNAME\dealer_master_events'
This question is related to
mysql
primary-key
auto-increment
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;
Yes, easy. Just run a data-definition query to update the tables, adding an AUTO_INCREMENT column.
If you have an existing database, be careful to preserve any foreign-key relationships that might already be there on the "artificially created" primary keys.
As long as you have unique integers (or some unique value) in the current PK, you could create a new table, and insert into it with IDENTITY INSERT ON. Then drop the old table, and rename the new table.
Don't forget to recreate any indexes.
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;
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;
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.
As long as you have unique integers (or some unique value) in the current PK, you could create a new table, and insert into it with IDENTITY INSERT ON. Then drop the old table, and rename the new table.
Don't forget to recreate any indexes.
As long as you have unique integers (or some unique value) in the current PK, you could create a new table, and insert into it with IDENTITY INSERT ON. Then drop the old table, and rename the new table.
Don't forget to recreate any indexes.
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;
Yes, easy. Just run a data-definition query to update the tables, adding an AUTO_INCREMENT column.
If you have an existing database, be careful to preserve any foreign-key relationships that might already be there on the "artificially created" primary keys.
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.
This worked for me (i wanted to make id primary and set auto increment)
ALTER TABLE table_name CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;
Yes, easy. Just run a data-definition query to update the tables, adding an AUTO_INCREMENT column.
If you have an existing database, be careful to preserve any foreign-key relationships that might already be there on the "artificially created" primary keys.
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.
As long as you have unique integers (or some unique value) in the current PK, you could create a new table, and insert into it with IDENTITY INSERT ON. Then drop the old table, and rename the new table.
Don't forget to recreate any indexes.
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.
This worked for me (i wanted to make id primary and set auto increment)
ALTER TABLE table_name CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;
Source: Stackoverflow.com