CODEWITHSUNDEEP
mysqlsql
OMAR
OMAR

Reputation: 3

add new column to existing table with auto_increment constrain

i want to add custId to an existing table Cust in which all the other fields are filled with data. I've used the following but i keep getting an error.

ALTER TABLE cust ADD custId INT NOT NULL AUTO_INCREMENT PRIMARY KEY

got the following error massage: 01735. 00000 - "invalid ALTER TABLE option"

Upvotes: 0

Views: 229

Answers (2)

Utsav
Utsav

Reputation: 8093

Run this

alter table cust add column custid integer primary key not null auto_increment;

This will

  • Add column custid as primary key which will be auto incremented further.
  • Populate custid starting with 1 in already existing data in cust table.

See the demo here.

http://sqlfiddle.com/#!9/eda52a/1

Upvotes: 0

Bfcm
Bfcm

Reputation: 2746

Use the right order:

ALTER TABLE `cust` ADD `custId` INT PRIMARY KEY AUTO_INCREMENT;

Upvotes: 1

Related Questions