Rectangle 27 0

mysql Insert auto increment primary key to existing table?


#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)
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 );

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.

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:

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.

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.

Note
Rectangle 27 0

mysql Insert auto increment primary key to existing table?


ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;
FIRST

+1, works in 5.1.51-ndb-7.1.9a-log, on table with engine MyISAM

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.

Note
Rectangle 27 0

mysql Insert auto increment primary key to existing table?


#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)
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 );

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.

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:

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.

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.

Note
Rectangle 27 0

mysql Insert auto increment primary key to existing table?


ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;
FIRST

+1, works in 5.1.51-ndb-7.1.9a-log, on table with engine MyISAM

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.

Note
Rectangle 27 0

mysql Insert auto increment primary key to existing table?


ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY
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:

It will make the new id column the first in the table as opposed to last, which is the default behaviour.

What does the FIRST at the end does?

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

Note
Rectangle 27 0

mysql Insert auto increment primary key to existing table?


ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY
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:

It will make the new id column the first in the table as opposed to last, which is the default behaviour.

What does the FIRST at the end does?

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

Note