Insert auto increment primary key to existing table

how to add auto increment column in existing table in mysql
alter column auto increment sql server
mysql auto increment primary key
alter auto increment in sql
mysql alter table auto_increment
auto increment in sql query
mysql auto increment start value
incorrect syntax near 'auto_increment'.

I am trying to alter a table which has no primary key nor auto_increment column. I know how to add an primary key column but I was wondering if it's possible to insert data into the primary key column automatically (I already have 500 rows in DB and want to give them id but I don't want to do it manually). Any thoughts? Thanks a lot.

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:


On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.

Add Auto-Increment ID to existing table?, Try this. ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY. Now if we would like to add "id" column as Auto Increment we can do that by using below statement. Remember that the Auto_Increment column need to be KEY. So you either defined as Primary Key or Unique Key. If your table already have Primary Key then you can defined as UNIQUE KEY. 1) When there is No primary key, then you can define as Primary Key

suppose you don't have column for auto increment like id, no, then you can add using following query:


If you've column, then alter to auto increment using following query:

 ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

SQL AUTO INCREMENT a Field, Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. column to be an auto-increment primary key field in the "Persons" table: ALTER TABLE Persons AUTO_INCREMENT=100; The SQL statement above would insert a new record into the "Persons" table. I want to add a new auto increment primary column to a existing table which has data. How do I do that? I first added a column and then try to add a sequence after that, I lost how to insert and make that column as primary key.

For those like myself getting a Multiple primary key defined error try:


On MySQL v5.5.31 this set the id column as the primary key for me and populated each row with an incrementing value.

Add autoincrement to existing PK, The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values,  We have a giant table in an Access database with over 500k records and no PK. Is it possible to insert an auto-incrementing primary key column into an already existing Access table?

yes, something like this would do it, might not be the best though, you might wanna make a backup

$get_query = mysql_query("SELECT `any_field` FROM `your_table`");

$auto_increment_id = 1;

while($row = mysql_fetch_assoc($get_query))
  $update_query = mysql_query("UPDATE `your_table` SET `auto_increment_id`=$auto_increment_id WHERE `any_field` = '".$row['any_field']."'");

notice that the the any_field you select must be the same when updating.

MySQL Tutorial :: 7.9 Using AUTO_INCREMENT, When you insert any other value into an AUTO_INCREMENT column, the column Updating an existing AUTO_INCREMENT column value in an InnoDB table  CREATE OR REPLACE TRIGGER auto_increment_tb_insert BEFORE INSERT ON auto_increment_tb FOR EACH ROW BEGIN SELECT auto_increment_tb_seq.nextval INTO FROM dual; END; Here we are creating a trigger named auto_increment_tb_insert which will be fired on each insertion on table auto_increment_tb. We are getting next auto incremented value

The easiest and quickest I find is this

ALTER TABLE mydb.mytable 
ADD UNIQUE INDEX mycolumnname_UNIQUE (mycolumname ASC);

Defining an Auto Increment Primary Key in SQL Server, Learn how to define an auto increment primary key in SQL Server. When a new record is inserted, we not only must manually enter a value for id , but we The solution turns out to be using two constraint options provided by SQL Server. Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted. The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the

How to Define an Auto Increment Primary Key in Oracle, ALTER TABLE books ADD ( CONSTRAINT books_pk PRIMARY KEY (id) );. Finally, we'll create our SEQUENCE that will be utilized later to actually generate the  How to Define an Auto Increment Primary Key in SQL Server Posted by AJ Welch Similar to MySQL, PostgreSQL, Oracle, and many other relational databases, SQL Server is best utilized when assigning unique primary keys to most database tables.

Add Auto Increment Primary Key Column Existing Table SQL Server , Each table can have only one AUTO_INCREMENT column. It must ALTER TABLE animals AUTO_INCREMENT=8; INSERT INTO animals (name) VALUES​  This article demonstrates how to add a primary key to an existing table in SQL Server using Transact-SQL. A primary key is a column that has been configured as the unique identifier for a given table. You would normally create a primary key constraint when you create the table, but you can also add a primary key to an existing table.

AUTO_INCREMENT, Run the following SQL Query to add a new column named id that acts as PRIMARY KEY and auto increments for each insert. ALTER TABLE students. ADD id  The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. When you insert a new record to the table, and the auto_increment field is NULL or DEFAULT, the value will automatically be incremented. This also applies to 0, unless the NO_AUTO_VALUE_ON_ZERO SQL_MODE is enabled. AUTO_INCREMENT columns start from 1 by default.