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 

4 comments:

  1. The ultimate goal of descriptive essay help services is to provide Descriptive Essay Writing Services and descriptive essay services since descriptive essay writing help seekers lack time to complete their custom descriptive essay writing services.

    ReplyDelete
  2. 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
  3. Such a wonderful information blog post on this topic allassignmentservices.com provides assignment service at affordable cost in a wide range of subject areas for all grade levels, we are already trusted by thousands of students who struggle to write their academic papers and also by those students who simply want buy assignment to save their time and make life easy.

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

    ReplyDelete