automatically insert auto increment primary key and values into existing table

Question

I am trying to alter a table which has no primary key nor auto_increment column. I know how to add an auto increment primary key column.

I already have 500 rows in the DB and want to give them ids but I don't want to do it manually.

How do I insert data into the primary key column automatically?

Answer 1

Drop the field for the primary key then recreate it auto incremented.

Answer 2

ALTER TABLE **tableName** MODIFY **tableNameID** **MEDIUMINT** NOT NULL **AUTO_INCREMENT**;

Here tableName is name of your table,

tableName is your column name which is primary has to be modified

MEDIUMINT is a data type of your existing primary key

AUTO_INCREMENT you have to add just auto_increment after not null

It will make that primary key auto_increment.

Answer 3

-- Create Departments Table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
    DepartmentName VARCHAR(100) NOT NULL
);

-- Create Employees Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DepartmentID INT,
    HireDate DATE,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Create Salaries Table
CREATE TABLE Salaries (
    SalaryID INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeID INT,
    SalaryAmount DECIMAL(10, 2) NOT NULL,
    EffectiveDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Answer 4

Yes, something like this would do it, 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']."'");
  $auto_increment_id++;
}

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

Answer 5

No existing primary key

ALTER TABLE `db`.`table` 
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);
;

Table already has an existing primary key'd column

(it will not delete the old primary key column)

ALTER TABLE `db`.`table` 
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
CHANGE COLUMN `prev_column` `prev_column` VARCHAR(2000) NULL ,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`);
;

Note: column must be first for auto increment which is why the FIRST command.

Answer 6

In order to make the existing primary key as auto_increment, you may use:

ALTER TABLE table_name MODIFY id INT AUTO_INCREMENT;

Answer 7

Well, you have multiple ways to do this: -if you don't have any data on your table, just drop it and create it again.

Dropping the existing field and creating it again like this

ALTER TABLE test DROP PRIMARY KEY, DROP test_id, ADD test_id int AUTO_INCREMENT NOT NULL FIRST, ADD PRIMARY KEY (test_id);

Or just modify it

ALTER TABLE test MODIFY test_id INT AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (test_id);

Answer 8

You can add a new Primary Key column to an existing table, which can have sequence numbers, using command:

ALTER TABLE mydb.mytable ADD pk_columnName INT IDENTITY 

Answer 9

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

ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST

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

Answer 10

Export your table, then empty your table, then add field as unique INT, then change it to AUTO_INCREMENT, then import your table again that you exported previously.

Answer 11

I was able to adapt these instructions take a table with an existing non-increment primary key, and add an incrementing primary key to the table and create a new composite primary key with both the old and new keys as a composite primary key using the following code:

DROP TABLE  IF EXISTS SAKAI_USER_ID_MAP;

CREATE TABLE SAKAI_USER_ID_MAP (
       USER_ID             VARCHAR (99) NOT NULL,
       EID                 VARCHAR (255) NOT NULL,
       PRIMARY KEY (USER_ID)
);

INSERT INTO SAKAI_USER_ID_MAP VALUES ('admin', 'admin');
INSERT INTO SAKAI_USER_ID_MAP VALUES ('postmaster', 'postmaster');

ALTER TABLE  SAKAI_USER_ID_MAP 
  DROP PRIMARY KEY, 
  ADD _USER_ID INT AUTO_INCREMENT NOT NULL FIRST, 
  ADD PRIMARY KEY ( _USER_ID, USER_ID );

When this is done, the _USER_ID field exists and has all number values for the primary key exactly as you would expect. With the "DROP TABLE" at the top, you can run this over and over to experiment with variations.

What I have not been able to get working is the situation where there are incoming FOREIGN KEYs that already point at the USER_ID field. I get this message when I try to do a more complex example with an incoming foreign key from another table.

#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)

I am guessing that I need to tear down all foreign keys before doing the ALTER table and then rebuild them afterwards. But for now I wanted to share this solution to a more challenging version of the original question in case others ran into this situation.

Answer 12

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

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

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.

Answer 13

The easiest and quickest I find is this

ALTER TABLE mydb.mytable 
ADD COLUMN mycolumnname INT NOT NULL AUTO_INCREMENT AFTER updated,
ADD UNIQUE INDEX mycolumnname_UNIQUE (mycolumname ASC);

Answer 14

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

ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST NOT NULL;

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