How to Add Auto_Increment column to existing Table - MySQL Developer Tutorial

How to Add Auto_Increment column to existing Table

It is very simple process to add Auto Increment column to existing table in MySQL. Let's say if you have a table with below definition and there is already some data added to the table.


CREATE TABLE `customer` (
   `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- Insert sample data
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


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

Alter table customer 
add column id int NOT NULL auto_increment Primary Key;

2) When there is Already Primary Key, then you can add new auto increment column as UNIQUE KEY.


Alter table customer 
add column id int NOT NULL auto_increment UNIQUE Key; 


How to add auto increment column to existing table - MySQL Tutorial 

3 comments:

  1. I feel there is a need to look for more about SQL and SSIS operations and make it believe more about this.

    SSIS Postgresql Read

    ReplyDelete
  2. This is very interesting, but it is necessary to click on this link: 2020 Keystone RV Fuzion 410

    ReplyDelete
  3. Nagaland Board 9th Textbook 2023 Nagaland 9th Class Textbook 2023 The Nagaland Board Class 9th Textbook 2023 will be declared after the board exams have been conducted. The Class 9th Board Exams are likely to be held Nagaland 9th Study Material 2023 in the month of March . The Nagaland Board Class 9th Textbook 2023 for the Nagaland Board Exams will be declared within a month after the exams have been conducted.

    ReplyDelete