Existing table add auto increment primary key - sql

Question

I have an existing product table which is already populated with 120 records and I have tried below SQL query:

ALTER TABLE product ADD product_id 
    INT PRIMARY KEY AUTO_INCREMENT

But it's given me the error:

Error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

ALTER TABLE product ADD product_id INT PRIMARY KEY AUTO_INCREMENT; — Ankit Sompura, May 16 '16 at 05:45
`ALTER TABLE product ADD column product_id INT PRIMARY KEY AUTO_INCREMENT;` — Sam Orozco, May 16 '16 at 05:47
Show your table description. A table can have only one primary key and if you are defining auto_increment column it has to be primary key i.e you cannot have one column as auto_increment and other column as primary key — undefined_variable, May 16 '16 at 05:47
I refer you to a prior post that might assist answering the question: http://stackoverflow.com/questions/8114535/mysql-1075-incorrect-table-definition-autoincrement-vs-another-key — Conrad Lotz, May 16 '16 at 05:54
For SQL SERVER: `ALTER TABLE product ADD ID INT PRIMARY KEY IDENTITY` — Rilcon42, Dec 07 '18 at 13:07

score 1 · Answer 1 · answered May 18 '16 at 15:11

1

Here is work around

create a simple column with autoincrement
browse the table and again make this newly added column as primary key

You are done !

:) Thanks to Guy who is not in Stackoverflow

answered May 18 '16 at 15:11

Ankit Sompura

765
6
10

score 0 · Answer 2 · answered May 16 '16 at 07:25

Friend If you try to do this in MSSQL then your script will be this. suppose your table is below

create table product
(
 name varchar(20),
 product_type varchar(20)
)

and suppose you entered some record in this table after that you want to add a column that name product_id with auto increment and primary key. Then you use this script that is below

alter table product add product_ID int primary key identity(1,1)

suppose this will be help full for you.

score -1 · Answer 3 · edited May 23 '17 at 12:00

There can only be one auto increment field per table. But, you could have a calculated field based on the auto increment field. Or, you could have an int field where you manage the sequence by front end code or by a trigger. And also you could use a sequence in SQL Server.

CREATE SEQUENCE MySequence START WITH 100;

CREATE TABLE MyTable
(
    RealIdentity INT IDENTITY(1,1),
    RandomCol NVARCHAR(100),
    FakeIdentity INT DEFAULT NEXT VALUE FOR MySequence
);

UPDATE - MySQL Way

I just noticed that your question is taged for MySQL. Above answer is for MS SQL. Here's how you'd do the same in MySQL.

How do I create a sequence in MySQL?

MySQL equivalent of Oracle's SEQUENCE.NEXTVAL

Existing table add auto increment primary key - sql

3 Answers3