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 

6 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
  5. Great Article Cyber Security Projects projects for cse Networking Security Projects JavaScript Training in Chennai JavaScript Training in Chennai The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete
  6. 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